topic Re: Pivot table cannot be changed by multiple date filter in App Development

Pivot table cannot be changed by multiple date filter

OliviaLai — Tue, 30 Nov 2021 16:25:32 GMT

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.

Re: Pivot table cannot be changed by multiple date filter

MayilVahanan — Thu, 18 Nov 2021 07:20:22 GMT

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)))

Re: Pivot table cannot be changed by multiple date filter

OliviaLai — Thu, 18 Nov 2021 07:46:40 GMT

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