topic Re: YTD for comparing multiple years in bar chart in App Development

YTD for comparing multiple years in bar chart

Sat, 08 Oct 2016 13:28:23 GMT

This is driving me crazy:

my chart looks like this:

The first dimension is: Year (Date)

Comparison can not be done as 2016 is incomplete, i.e. we are still in 2016.

How do I display data for all years up to the day/month of the last input for the current year (2016)?

I´ve been reading all help files, blogs, etc but could not solve this in three days!!

There seems to be a very convoluted / insanely complicated scripting language in Qlik and the diagrams just don´t look right.

Another thing: why are values not shown in the stacks?? There is enough room!

cheers

Re: YTD for comparing multiple years in bar chart

sunny_talwar — Sun, 09 Oct 2016 04:55:00 GMT

May be create a YTD Flag in the script which goes through the maximum date in the most recent year. May be something like this:

Table:

LOAD Date,

InYearToDate(SetDateYear(Date, Year(Today())), Today(), 0) as YTD_Flag;

LOAD Date(MakeDate(2010, 12, 31) + IterNo()) as Date

AutoGenerate 1

While IterNo() <= Today() - MakeDate(2010, 12, 31);

Here, each year until October 9th the YTD_Flag = -1 and rest of year YTD_Flag = 0. So now in your set analysis, you can just do this:

Sum({<YTD_Flag = {-1}>} Measure)

or this if you wish to further drill down on selections you make

Sum({<YTD_Flag *= {-1}>} Measure)

Re: YTD for comparing multiple years in bar chart

Sun, 09 Oct 2016 09:54:02 GMT

If I do the above, Qlik Sense says "invalid Dimension"

I probably put something in the wrong place, so just to repeat:

1) I added the text:

Table:

LOAD Date,

InYearToDate(SetDateYear(Date, Year(Today())), Today(), 0) as YTD_Flag;

LOAD Date(MakeDate(2010, 12, 31) + IterNo()) as Date

AutoGenerate 1

While IterNo() <= Today() - MakeDate(2010, 12, 31);

into the script. I did not put it at the end of the auto-generated load script as I would have to unlock the script and there was a warning that I could not use the data manager when I unlock it and it can not be re-locked again. Instead, I added a new section in the script where I put the above text.

After saving the script, I reloaded my data.

Then I put

Count({<YTD_Flag *= {-1}>} Operateur)

into my field for the second dimension, the first being Year(Date).

When I do that, it says: "error in formula"??

I also tried variations, like

Year({<YTD_Flag *= {-1}>} Date)

and put that into pretty much all boxes that I could find.

Sometimes that would give me an "invalid dimension", at other times an "internal error"

Re: YTD for comparing multiple years in bar chart

sunny_talwar — Mon, 10 Oct 2016 11:16:08 GMT

Why would you put Count() in your dimension? I wanted you to use the set analysis in your expression. Can you try this in your expression?

Re: YTD for comparing multiple years in bar chart

maxgro — Mon, 10 Oct 2016 11:35:27 GMT

maybe something like

count({$ <Date={"=DayNumberOfYear(Date)<=DayNumberOfYear(Today())"}>} Measure)

Re: YTD for comparing multiple years in bar chart

Wed, 12 Oct 2016 12:49:35 GMT

Big thank you to Massimo Grossi, that´s it!!!!!