Set anaylisis for Pivot Table

Nikomotte — Sun, 05 Feb 2023 20:12:41 GMT

Hello to all

I have a list of cusomer with quantity, date, region, sub region and Sales responsible. I woul like to do a pivot table with region, sub region and Sales reps in row and column of month. The data to be displayed are the sum of quantities realized in 2023 for the customer that did not had any sales during the last to years (Q= 0)

i have tried the follong formula bu it does not work at all

=if(aggr(sum({< YEAR= {"<=$(Vannee-1)"},NO_CUST=,MONTH=>}Q),NO_CUST<=0,
sum({< YEAR= {"$(Vannee)"}, NO_CUST=, MONTH=>}
Q),0)

I have added in excel cause it shows in the pivot what i want.

Thanks in advance....

Re: Set anaylisis for Pivot Table

E_Røse — Mon, 06 Feb 2023 21:06:51 GMT

If the not active previous years condition does not need to be dynamic, I would use the load script to calculate that. Could something like the following work?

Left keep(DATA) Load NO_CUST, if(sum(if(YEAR<=2022,Q,0))>0, 1,0) as [Active previous year flg] resident DATA Group By NO_CUST;

with measure:

sum({$<[Active previous year flg]={0}, YEAR={'2023}>} Q).

topic Set anaylisis for Pivot Table in App Development

Set anaylisis for Pivot Table

Re: Set anaylisis for Pivot Table