Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
I would like to change the pivot table by year/quarter/moth filter to calculate the past value.
Here's the formula of past value.
sum({<yearmonth.autoCalendar.YearMonth= {">=$(=Addyears(min(yearmonth.autoCalendar.YearMonth),-1))<=$(=Addyears(max(yearmonth.autoCalendar.YearMonth),-1))"}> } ($(=vMetrics))) |
It woks only when I choose the yearmonth(value is not equal to 0).
If I choose year, yearquarter and yearmonth together, it cannot work (value is euqual to 0).
Can someone help me please. Thank you.
Hi
Try to exclude those fields from the expression and verify it
sum({<yearmonth.autoCalendar.YearMonth= {">=$(=Addyears(min(yearmonth.autoCalendar.YearMonth),-1))<=$(=Addyears(max(yearmonth.autoCalendar.YearMonth),-1))"}, YearField=, QuarterField=> } ($(=vMetrics)))
Hello!
It works! Thank you very much!
Here's my further question
How to revise the growth rate formula?
((Sum({<cust_name = {"=Rank(Sum(z9neta)) <= ($(=vTopValues))"}>} z9neta))/sum({<cust_name = {"=Rank(Sum(z9neta)) <= ($(=vTopValues))"}, yearmonth.autoCalendar.YearMonth= {">=$(=Addyears(min(yearmonth.autoCalendar.YearMonth),-1))<=$(=Addyears(max(yearmonth.autoCalendar.YearMonth),-1))"}>} z9neta))-1 |