Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
How to calculate number of customers who has continuous 3 months sales like in below table? Thanks.
eg. assume now is 202012, expected result is 1, customer A.
Year months customer A, customer B
202012 100, 100
202011 100, 0
202010 100, 0
@GraceGao one solution is to use
for this example : first Crosstable
CrossTable(customer, Sales)
LOAD * INLINE [
Year months,customer A,customer B, customer C
202012,100,100,10
202011,50,0,20
202101,60,0,30
];
this function :
=count({<customer={"=sum({<[Year months]={[$(=Date(today(),'YYYYMM'))]}>} Sales)<>0 and sum({<[Year months]={[$(=Date(addmonths(today(),-1),'YYYYMM'))]}>} Sales)<>0 and sum({<[Year months]={[$(=Date(addmonths(today(),-2),'YYYYMM'))]}>} Sales)<>0"}>}distinct customer)
output:
Thanks Zarra