Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
want to build a pivot table or water fall chart to only show those discontented account/customers:
I have :
account
sales (measure)
sales time,
how I can edit expression to showcase discontinued accounts within 6 months and 12 months.
To put another way, I want to list all those accounts that does not have any sales within 6 months or 12 months
Thanks
GrayGiles, much appreciated
Three followed questions:
1. do I have to use set statement for both dimension and measure? or just either one of them
2. Tried to insert your measure, maybe I missed something, it did not go thru. would you please make a new expression with
[Customer Full Name]
[Contract Sales]
[Time]
3. I am trying to tease out a waterfall chart showing all those customers with 0 sales during past 3 month with variances against their previous 3 months sales (6month as of current date).
I created a variable for total 3 month sales and put into set expression in order to filter out all those customers with 0 sales of 3 month. However, it works for some customers but not all of them. Still some customers with positive sales are there. Am I missing something? Here below is my expression for measure:
Num(Sum({$<v3monthsales={"=0"},SaleYear=,SaleQuarterYear=, SaleMonthYear=,SaleDate = {">=$(=addmonths(Monthstart((vSaleMaxDate)),-3))<=$(=addmonths(Monthend((vSaleMaxDate)),-1))"} >}[Contract Sales])
-
Sum({$<SaleYear=,SaleQuarterYear=, SaleMonthYear=,SaleDate = {">=$(=addmonths(Monthstart((vSaleMaxDate)),-6))<=$(=addmonths(Monthend((vSaleMaxDate)),-4))"} >}[Contract Sales]),'$#,##0;($#,##0)')
1) In general, no, you don't have to use both. You could use one or the other. But, it depends on what else you want to show in your table or chart.
2)
=aggr(only({1<[Customer Full Name]-={'=sum({1<[Time]={">=$(=AddMonths(Today(),-6))"}>} [Contract Sales])>0'}>} [Customer Full Name]), [Customer Full Name])
=sum({$<[Customer Full Name]-={'=sum({1<[Time]={">=$(=AddMonths(Today(),-6))"}>} [Contract Sales])>0'}>} [Contract Sales])
3) You can't use a variable as the left argument in set analysis.
You might try this as your Dimension.
=aggr(only({1<[Customer Full Name]-={'=sum({1<[Time]={">=$(=AddMonths(Today(),-3))"}>} [Contract Sales])>0'}>} [Customer Full Name]), [Customer Full Name])
and this as your measure:
=sum({$<[Time]={">=$(=AddMonths(Today(),-6)<$(=AddMonths(Today(),-3))"}>} [Contract Sales])
The Dimension will filter out customers with sales in the last 3 months and the measure will calculate the sales for the remaining customers (those with no sales in the last 3 months) in the periods 4 through 6. This would affectively be the variance since the 3 month sales is 0.
If you want to limit the accounts in a dimension expression, you would use:
=aggr(only({1<account-={'=sum({1<[sales time]={">=$(=AddMonths(Today(),-6))"}>} measure)>0'}>} account), account)
If you want to limit the accounts in a measure expression, you would use:
=sum({$<account-={'=sum({1<[sales time]={">=$(=AddMonths(Today(),-6))"}>} measure)>0'}>} measure)
Note the minus sign between account and = in the set analysis. It's kind of hard to see, but it it critical to getting the expressions to work properly.
This is for sales within 6 months. To get Also, you may need to adjust the formula to calculate the date comparison, depending on the format of [sales time].
GrayGiles, much appreciated
Three followed questions:
1. do I have to use set statement for both dimension and measure? or just either one of them
2. Tried to insert your measure, maybe I missed something, it did not go thru. would you please make a new expression with
[Customer Full Name]
[Contract Sales]
[Time]
3. I am trying to tease out a waterfall chart showing all those customers with 0 sales during past 3 month with variances against their previous 3 months sales (6month as of current date).
I created a variable for total 3 month sales and put into set expression in order to filter out all those customers with 0 sales of 3 month. However, it works for some customers but not all of them. Still some customers with positive sales are there. Am I missing something? Here below is my expression for measure:
Num(Sum({$<v3monthsales={"=0"},SaleYear=,SaleQuarterYear=, SaleMonthYear=,SaleDate = {">=$(=addmonths(Monthstart((vSaleMaxDate)),-3))<=$(=addmonths(Monthend((vSaleMaxDate)),-1))"} >}[Contract Sales])
-
Sum({$<SaleYear=,SaleQuarterYear=, SaleMonthYear=,SaleDate = {">=$(=addmonths(Monthstart((vSaleMaxDate)),-6))<=$(=addmonths(Monthend((vSaleMaxDate)),-4))"} >}[Contract Sales]),'$#,##0;($#,##0)')
1) In general, no, you don't have to use both. You could use one or the other. But, it depends on what else you want to show in your table or chart.
2)
=aggr(only({1<[Customer Full Name]-={'=sum({1<[Time]={">=$(=AddMonths(Today(),-6))"}>} [Contract Sales])>0'}>} [Customer Full Name]), [Customer Full Name])
=sum({$<[Customer Full Name]-={'=sum({1<[Time]={">=$(=AddMonths(Today(),-6))"}>} [Contract Sales])>0'}>} [Contract Sales])
3) You can't use a variable as the left argument in set analysis.
You might try this as your Dimension.
=aggr(only({1<[Customer Full Name]-={'=sum({1<[Time]={">=$(=AddMonths(Today(),-3))"}>} [Contract Sales])>0'}>} [Customer Full Name]), [Customer Full Name])
and this as your measure:
=sum({$<[Time]={">=$(=AddMonths(Today(),-6)<$(=AddMonths(Today(),-3))"}>} [Contract Sales])
The Dimension will filter out customers with sales in the last 3 months and the measure will calculate the sales for the remaining customers (those with no sales in the last 3 months) in the periods 4 through 6. This would affectively be the variance since the 3 month sales is 0.