Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
Look at HIC's response here to create a flag
Hi Sunny,
Just saw your reply to this post, late, sorry. I do not see a link to the HIC's solution for creating a flag in the script. When you get a chance can you send the link or clarify?
Thanks,
Dave
Sorry, here is the link
Thanks Sunny!
On Mon, Jan 8, 2018 at 10:59 AM, Sunny Talwar <qcwebmaster@qlikview.com>