Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Coloful_Black
Contributor III
Contributor III

set analysis expression for inactive customers with no current 3 month sales

stuck on this ...at a bar chart, want to show inactive customers with no sales during the past three month.

 

let's say I have time range as "Saledate = {">=$(=addmonths(Monthstart((vSaleMaxDate)),-3))<=$(=addmonths(Monthend((vSaleMaxDate)),-1))"}"

How do I add this into below so that I could have the set expression to have those customers who do not have any sales during the saledate period? 

sum({<[Customer Name]={"=sum([Sales]) = 0"}>} [Sales])

 

or maybe there is a better alternative without using any of these...

 

 

Labels (2)
2 Solutions

Accepted Solutions
BrunPierre
Partner - Master
Partner - Master

Perhaps

=aggr(only({<[Customer Name] = E({<Saledate ={">=$(=addmonths(max(Saledate ),-3)) <=$(=max(Saledate ))"}>}) * {"=Sum([Sales])=0"}>}[Customer Name]), [Customer Name])

View solution in original post

zacsyuan
Contributor II
Contributor II

I would love to set the time ...which make the set expression a bit more simple...

 

=Aggr(

if(
aggr(Max(Saledate), [Customer Name])=monthstart(addmonths(today(),-3))
,[Customer Name]),[Customer Name])

 

or using your set variable

if(
aggr(Max(Saledate), [Customer Name])={">=$(=addmonths(Monthstart((vSaleMaxDate)),-3))<=$(=addmonths(Monthend((vSaleMaxDate)),-1))"}")
,[Customer Name]),[Customer Name])

 

pretty much it says if that customer 's most recent sales date is the month prior to current 3 month, show them up....

 

 

View solution in original post

3 Replies
BrunPierre
Partner - Master
Partner - Master

Perhaps

=aggr(only({<[Customer Name] = E({<Saledate ={">=$(=addmonths(max(Saledate ),-3)) <=$(=max(Saledate ))"}>}) * {"=Sum([Sales])=0"}>}[Customer Name]), [Customer Name])

zacsyuan
Contributor II
Contributor II

I would love to set the time ...which make the set expression a bit more simple...

 

=Aggr(

if(
aggr(Max(Saledate), [Customer Name])=monthstart(addmonths(today(),-3))
,[Customer Name]),[Customer Name])

 

or using your set variable

if(
aggr(Max(Saledate), [Customer Name])={">=$(=addmonths(Monthstart((vSaleMaxDate)),-3))<=$(=addmonths(Monthend((vSaleMaxDate)),-1))"}")
,[Customer Name]),[Customer Name])

 

pretty much it says if that customer 's most recent sales date is the month prior to current 3 month, show them up....

 

 

Coloful_Black
Contributor III
Contributor III
Author

thanks..I love urs...easier to grasp