Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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.
=Sum({<PeriodId = { $(=Max(PeriodId))},Year={$(=Max(Year))}, MonthField>}Sales
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
Thanks Sunny. This really helps