2 Replies Latest reply: Oct 27, 2017 1:06 PM by Sunny Talwar RSS

    Creating a (true) YTD measure

    David Dumas

      I wanted to share something in a post from Sunny Talwar that allowed me to create a true YTD measure which ignores selections (which is what you need) that users make on any date selections except for year.  I have not seen this anywhere in the docs, or books - I have many.  This indeed works.  Thanks Sunny!:

       

      If there are any other solutions to this, I would love to see them.

       

      Sum({<PeriodId = {"<= $(=Max({<Quarter,Month>}PeriodId))"},Year={"$(=Max({<Quarter,Month>}Year))"},Month=>}Sales)


      Adding the {<Month>} modifiers "fixes"  what I would call a bug in set analysis.  I love Qlik, but creating time intelligence functions really should not be that hard  


      Thanks,


      Dave

        • Re: Creating a (true) YTD measure
          Sunny Talwar

          I guess it depends on what you call a true YTD. What if you user wants to see YTD based on Month selection? For example, the max month is Oct, but they want to see YTD June.... this is till true YTD... In fact this is a might be more intuitive to a new user as he would expect to see YTD based on the month he has selected.... in this case, the expression will be this

           

          Sum({<PeriodId = {"<= $(=Max(PeriodId))"},Year={"$(=Max(Year))"},Month=, Quarter>}Sales)

           

          But if the goal is to see YTD regardless of any month or quarter selected, then why not create a flag in the script? this would make the expression so much more simpler without adding set analysis within set analysis?