Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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....
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).