topic Re: Creating a (true) YTD measure in QlikView

Creating a (true) YTD measure

dadumas — Fri, 27 Oct 2017 16:45:39 GMT

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 — Fri, 27 Oct 2017 17:05:35 GMT

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?

Re: Creating a (true) YTD measure

sunny_talwar — Fri, 27 Oct 2017 17:06:49 GMT

Look at HIC's response here to create a flag

Re: Creating a (true) YTD measure

dadumas — Mon, 08 Jan 2018 14:46:14 GMT

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

Re: Creating a (true) YTD measure

sunny_talwar — Mon, 08 Jan 2018 15:59:15 GMT

Sorry, here is the link

YTD(Year to date)

Re: Creating a (true) YTD measure

dadumas — Mon, 08 Jan 2018 16:51:26 GMT

Thanks Sunny!

On Mon, Jan 8, 2018 at 10:59 AM, Sunny Talwar <qcwebmaster@qlikview.com>