topic Re: YTD Expression not working properly in QlikView

YTD Expression not working properly

Anonymous — Sat, 14 May 2016 04:41:03 GMT

Hi,

I am trying to find out Year To Date Value based on the below expression:

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

Two Years - 2011 and 2012

PeriodId-1 to 12 for Year 2011 and 13 to 24 for Year 2012.

Here "<= $(=Max(PeriodId))" Part is not working as expected. If I split that expression like $(=Max(PeriodId)) and test, it works. But as a whole "<= $(=Max(PeriodId))" is not working. If I select Year 2011 and September which has PeriodId as 9, I am suppoed to get the results from Jan to Sep because of the condition "<= 9 " but instead I am getting the value of september.

I do not know what is wrong with the expression. Can someone please help me on this.

Re: YTD Expression not working properly

sunny_talwar — Sat, 14 May 2016 11:43:56 GMT

Is September a separate month field? I think you need to ignore selection in Month and it might work for you:

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

Re: YTD Expression not working properly

Anonymous — Sun, 15 May 2016 02:01:18 GMT

Hi Sunny,

It works now. Thanks for the help.

I have another question related to this.

Ignoring a dimension means anything we selected in that dimension List Box will be ignored in the set analysis.

But here:

=Sum({<PeriodId = {"<= $(=Max(PeriodId))"},Year={"$(=Max(Year))"},Month=>}Sales) Works as expected. If I select a Month dimension in the list box it shows the correct Year to Date value

Whereas if i use =Sum({<PeriodId = {"<= $(=Max(PeriodId))"},Year={"$(=Max(Year))"}>}Sales) by removing the "Month=", and select a Month dimension in the list box, it shows the result for that Month alone not the YTD value.

Could you please explain, how adding the "Month=" changes the value here?

Also, as per definition, if we use Dimension= in the set analysis, then that dimension selection should be ignored right? But in the above two different cases, when i select a Month the value changes. How it is possible? Please help with an explanation.

Re: YTD Expression not working properly

Anonymous — Sun, 15 May 2016 05:21:16 GMT

=Sum({<PeriodId = { $(=Max(PeriodId))},Year={$(=Max(Year))}, MonthField>}Sales

YTD Expression not working properly

Re: YTD Expression not working properly

sunny_talwar — Sun, 15 May 2016 19:06:13 GMT

As you mentioned that you are making a selection in the month field, unless you ignore the selection your expression will filter to just that single month. Now the question is how is it able to show the desired result in the first case. Well you might have ignored Month in the first expression, but PeriodIdis related to Month. As soon as you selected a month, the value for Max(PeriodId) changed.

To see this change, I would suggest you to add a text box object for Max(PeriodId) and see its value changing based on selection of Month.

Does it clarify a little bit? or I answered something you already knew and did not address the question you had? Let me know and we can work on this a little more.

Best,

Sunny

Re: YTD Expression not working properly

Anonymous — Tue, 17 May 2016 00:08:21 GMT

Thanks Sunny. This really helps