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