Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have a table as below:
yearid | custid | salesorder | amt |
2012 | A | 1 | 15 |
2012 | A | 2 | 10 |
2012 | A | 3 | 16 |
2009 | B | 1 | 12 |
2010 | B | 2 | 15 |
2012 | B | 3 | 12 |
2013 | B | 4 | 14 |
And I use the aggr function to setup a expression to calculate the min salesorder group by custid,yearid,just like below:
yearid | custid | salesorder | amt | Min_Salesorder_Year |
2012 | A | 1 | 15 | 1 |
2012 | A | 2 | 10 | 1 |
2012 | A | 3 | 16 | 1 |
2009 | B | 1 | 12 | 1 |
2010 | B | 2 | 15 | 2 |
2012 | B | 3 | 12 | 3 |
2013 | B | 4 | 14 | 4 |
And I want to have another expression to calculate the Min_Salesorder_Year-1 for Year-1,looks like:
yearid | custid | salesorder | amt | Min_Salesorder_Year | Min_Salesorder_Year-1 |
2012 | A | 1 | 15 | 1 | - |
2012 | A | 2 | 10 | 1 | - |
2012 | A | 3 | 16 | 1 | - |
2009 | B | 1 | 12 | 1 | - |
2010 | B | 2 | 15 | 2 | 1 |
2012 | B | 3 | 12 | 3 | - |
2013 | B | 4 | 14 | 4 | 3 |
Anyone has idea of how to write the expression about the "Min_Salesorder_Year-1"?
Thanks a lot.
Try if(yearid-above(total yearid)=1,above(total [Min_Salesorder_Year]))
For example,
yearid | custid | salesorder | amt | Min_Salesorder_Year | Min_Salesorder_Year-1 |
2009 | B | 1 | 12 | 1 | - |
2010 | B | 2 | 15 | 2 | 1 |
I need the value of Min_Salesorder_Year-1 in 2010 equivalent to Min_Salesorder_Year in 2009. It means the value 1 in green same as that in red.
Try if(yearid-above(total yearid)=1,above(total [Min_Salesorder_Year]))
exactly
Thank you very much, it works.
Hi G Wassenaar,
First of all, thank you for your answer,actually I intend to use this expression as a calculated dimension to classify the customers, but when I put it into the calculated dimension ,only the aggr() dimension works.
What I want is, for example, a customer who bought in both 2012 and 2011(continuous two years) is considered as a existing customer,and a customer who bought in both 2012 and 2010(not continuous two years) is considered as a reactivated customer,a customer who only bought in current year is considered as a new customer.
And in this case, do you have any good ideas?