Create a dynamic time scale in a column chart


#1

I want to create a dynamic time scale on my column chart, so you can select if you want to see the trend per year, month, or day. How do I do this?

Regards
Daniela


#2

Hi Daniela,

To have it formatted in the right way (2007 as year, 2007-01 as month, and 2007-01-01 as day), you have to do the following:

for days:
YEAR(“EKKO”.“AEDAT”)
||’-’ ||
CASE WHEN
MONTH(“EKKO”.“AEDAT”) < 10 THEN
‘0’||MONTH(“EKKO”.“AEDAT”)
ELSE MONTH(“EKKO”.“AEDAT”)||’’ END
||’-’ ||
CASE WHEN
DAY(“EKKO”.“AEDAT”) < 10 THEN
‘0’||DAY(“EKKO”.“AEDAT”)
ELSE DAY(“EKKO”.“AEDAT”)||’’ END

(remove the day or day and month part for month and year)

The trick is that you have to concatenate the ELSE statement with an empty string, to make sure it is the same data type as what is returned in the THEN statement. Otherwise it won’t work.

Best,
Niklas


#3

I think we can also use variables, I would create one variable with possible values like ROUND_DAYS,WEEK orYEAR and let user select the option he wants.
Then using that variable in column charts dimension as preceding to the date column something like <%=Variable%>(“EKKO”.“AEDAT”).

I am also newbie so no guarantee this will work :slight_smile: