Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
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