Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Long time listener, first time caller.
I have a set analysis function that sums the KPIValue for a specific period (3 months back).
The function:
sum({1<CALENDAR.calendarDate={">=$(=addmonths(monthend(max(CALENDAR.calendarDate))+1,-3)) <=$(=max(CALENDAR.calendarDate))"}>}IF(kpiName =SelectedKPI,kpiValue))
Because of "1", the function doesn't take into account other selections that I can do on the dashboard.
If I change "1" to "$", I will only get the sum of the current month selection.
If I remove "1", I get the same (sum of current month selection).
Mind you, I have only enabled Month, and Year selections from the time dimension.
How can I change the function above to be able to take into consideration other selections from existing List Boxes that I have?
Sorry, you need to remove the 1 set identifer in my previous post:
sum({<CALENDAR.calendarDate={">=$(=addmonths(monthend(max(CALENDAR.calendarDate))+1,-3)) <=$(=max(CALENDAR.calendarDate))"}, Year=, Month= >}
IF(kpiName =SelectedKPI,kpiValue))
You probably need to clear selections in all calendar fields the user may select in, e.g. Year and Month:
sum({1<CALENDAR.calendarDate={">=$(=addmonths(monthend(max(CALENDAR.calendarDate))+1,-3)) <=$(=max(CALENDAR.calendarDate))"}, Year=, Month= >}
IF(kpiName =SelectedKPI,kpiValue))
Yes, I have year and month available for selection.
But adding that info in the formula (as you suggested), doesn't change a thing. I think that there is similar case on this forum, but it doesn't work for me.
The calculation returns the value for the sum of the 3 months, irrelevant of the other list box selections.
Please share a sample app. There could be many other things in your app.
Sorry, you need to remove the 1 set identifer in my previous post:
sum({<CALENDAR.calendarDate={">=$(=addmonths(monthend(max(CALENDAR.calendarDate))+1,-3)) <=$(=max(CALENDAR.calendarDate))"}, Year=, Month= >}
IF(kpiName =SelectedKPI,kpiValue))
Thank you!