Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

YTD Expression not working properly

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.

5 Replies
sunny_talwar

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)

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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

sunny_talwar

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

Anonymous
Not applicable
Author

Thanks Sunny. This really helps