5 Replies Latest reply: Oct 12, 2016 8:49 AM by Holger Baatz RSS

    YTD for comparing multiple years in bar chart

    Holger Baatz

      This is driving me crazy:

      my chart looks like this:

      338a4459225f4f18a74471d2a624bdc5.png

      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

          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
              Holger Baatz

              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
              Massimo Grossi

              maybe something like

               

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