set expression or smart way to identify inactive customers in time A but not time B

[coloful_architect]

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

[Aditya_Chitale]

@coloful_architect ,

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

[Aditya_Chitale]

@coloful_architect ,

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

[coloful_architect]

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.