Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, all.
I'm hoping someone can help me with my following chart expression:
if(isnull([Capacity_Date.autoCalendar.Year]),
Sum( { $< [Capacity_Date.autoCalendar.InYTD]={1}, [Capacity_Date.autoCalendar.YearsAgo]={1} > } [Deliverables] ) -
Sum( { $< [Capacity_Date.autoCalendar.MonthRelNo]={0}, [Capacity_Date.autoCalendar.YearsAgo]={1} > } [Deliverables] ), Sum({<Cap_Year={2017}>}Deliverables))
What I'm trying to achieve is to show a YTD value for the previous year if no filters are applied - when filters are applied, I want to see the appropriate values corresponding to the filter selections. This is being displayed in a KPI with 2 measures - the other measure shows CY YTD if no filters are applied and appropriate values corresponding to the filter selections when the filters are used. Now, this does work, for the most part. The problem is that when I select months, quarters, etc. spanning years, the values from the 2 KPIs default back to their YTD values.
Is there a better way to write my initial "if" statement? I'm using calendar measures to define my date filters and my set analysis expressions but am open to writing these expressions differently.
Thanks!
You may need to disregard your field selections if you don't want to see the reflections of those selections in your expression value, smthg like this -
f(isnull([Capacity_Date.autoCalendar.Year]),
Sum( { $< [Capacity_Date.autoCalendar.InYTD]={1}, [Capacity_Date.autoCalendar.YearsAgo]={1},Month=,Year=,Quarter= > } [Deliverables] ) -
Sum( { $< [Capacity_Date.autoCalendar.MonthRelNo]={0}, [Capacity_Date.autoCalendar.YearsAgo]={1},Month=,Year=,Quarter= > } [Deliverables] ), Sum({<Cap_Year={2017},Month=,Year=,Quarter=>}Deliverables))
Thank you very much for responding but that didn't seem to work. I appreciate the help though!