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
Thanks Aditya, appreciate your trial load and test table. would you pls try the data attached at spreadsheet?
the ultimate goal is to built a table chart to show customer ( those customers who do have sales in Q1 but no sales in Q2) ) , sales rep, and Q1 sales (lost sales)
for example of Product A at attached, if we unselect 0 and negative values at Q1 units and then select all negative and 0 sales , you get 60 customers with total units 6,340
so ideally, the final chart table is like this
Customer | Sales Rep | Q1 Sales |
cus 1 | A | … |
cus2 | B | … |
… | C | … |
cus 60 | D | … |
total lost sales in | 6,340 |
same logic for product B, I want to get 56 inactive customers with total 4902 Q1 sales. the same for product C and D, I put the number of total inactive customers and Q1 sales by each product at each tab.
current my approach is using aggr for the dimension of customers. like this
Aggr(if(
aggr(Max(Saledate), [Customer]) > '12/01/2023' and aggr(Max(Saledate), [Customer]) < '04/01/2024'
,[Customer]),[Customer])
and put a simple set expression for measurement for only showing Q1 sales.
the problem is this approach can not address those customers like month 1 has positive sales but month 3 has negative sales, so the quarter sales would be 0 since they cancel each out. and it could not address the issue of those customers have returned value/ negative value.