Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I try to build a table to show the count of those customers are no longer active in Q2. (see the demo table)
That is to say, those customer should have larger than 0 sales in Q1; that gives A B C D G H
since E and F 's Q1 sales is 0.
Then , among the rest of customers, we rule out any customers who have sales larger than 0 in Q2
and the final list is C D H
blow is a simple demo table. dimension filed is Cus, Month, measurement filed is sales
would you pls help me create a count formula ? or aggr to show the customer C D H
Much appreciated it
Cus | Dec-23 | Jan-2024 | Feb-2024 | Mar-2024 | Apr-2024 | May-2024 | Jun-2024 |
A | 90 | 1,550 | 1,300 | 1,280 | 0 | ||
B | 100 | 1,490 | 1,000 | 1,270 | (500) | 500 | |
C | 70 | 950 | 820 | 930 | 720 | 660 | 1,180 |
D | 90 | 950 | 850 | 830 | 740 | 760 | 670 |
E | 90 | 0 | 780 | -780 | 1,360 | 980 | 1,170 |
F | 0 | 0 | 0 | 0 | 2,260 | 770 | 720 |
G | 0 | 0 | 0 | -690 | 1,010 | 620 | 690 |
H | 100 | 500 | 670 | 720 | 750 | 580 | 350 |
If we consider quarter 1 consists of Jan 2024 to Mar 2024, Still Customer G has sales less than 0. Then how is this condition evaluating true :
"That is to say, those customer should have larger than 0 sales in Q1; that gives A B C D G H".
Regards,
Aditya
You can try below solution:
1. Calculate Customer & Quarter wise grouped Sales through script and join it with your Fact table:
test:
load * ,
'Q' & ceil(month(NewMonth)/3) as QuarterNumber;
load * ,
monthname(Date#(Month,'MMM YYYY')) as NewMonth;
load * inline
[
Customer, Month, Sales
A,Jan 2024,1550
A,Feb 2024,1300
A,Mar 2024,1280
A,Apr 2024,0
A,May 2024,0
A,Jun 2024,0
B,Jan 2024,1490
B,Feb 2024,1000
B,Mar 2024,1270
B,Apr 2024,500
B,May 2024,500
B,Jun 2024,0
C,Jan 2024,0
C,Feb 2024,780
C,Mar 2024,-780
C,Apr 2024,1360
C,May 2024,980
C,Jun 2024,1170
];
left join (test)
ActiveCustomers:
load
Customer,
QuarterNumber,
sum(Sales) as ActiveSales
Resident test group by Customer,QuarterNumber;
2. Use this expression in chart to identify inactive customers:
=if(wildmatch(Aggr(Concat(ActiveSales,'-'),Customer),'*-0-*'),null(),Customer)
Output:
If this solution doesn't work for you, please share sample data.
Regards,
Aditya