Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
Perhaps
=aggr(only({<[Customer Name] = E({<Saledate ={">=$(=addmonths(max(Saledate ),-3)) <=$(=max(Saledate ))"}>}) * {"=Sum([Sales])=0"}>}[Customer Name]), [Customer Name])
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....
Perhaps
=aggr(only({<[Customer Name] = E({<Saledate ={">=$(=addmonths(max(Saledate ),-3)) <=$(=max(Saledate ))"}>}) * {"=Sum([Sales])=0"}>}[Customer Name]), [Customer Name])
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....
thanks..I love urs...easier to grasp