Problem: Rolling 12-Month/current period with fiscal year and number of customers with negative profit

[keitel2015]

Hello guys,

I have a problem within my application which I unfortunately cannot share. Hence I created a similar application to show you what I mean.

I would like to count all customers which have a negative profit over the last 12 month, e.g. last both month are positive but the last 12 month are totally negative then count. I would like to swap between current period, Rolling 6-Month, Rolling 12-Month too, e.g. the last 12 month of rolling 12 month are totally negative or last 12 month of current values are negative. The last point is that I would like to show the last 12 Month in a bar chart with the correct numbers.

I have two KPIs in my example data sets (profit and sales revenue). I used the AsOfTable-concept from Henric to get my rolling results. In my shared application you can find two charts: Sales revenue table and negative customer bar chart. The sales revenue table works very well but not my bar chart. If you open the application you see the rolling 12 month and if you click on current period you get the correct values for sales revenue but not for negative customers. If you click on a special date, e.g. jun 2015 you will get the correct values. So what is wrong?

Thank you very much in advance!

Best regards,

Sebastian

[keitel2015]

I think that I have the solution:

 count(DISTINCT{<[Fiscal Date]={">=$(=MonthStart(Max([Fiscal Date]), -12))<=$(=MonthEnd(Max([Fiscal Date])))"}, [Profit]={"=sum([Profit])<0"}>} [Customer ID])

[settu_periasamy]

I think, your expression gives total number [Customer ID] Count. Not the Negative Profit Customer Count.

For Jun' 2015, It should be '9'. But it shows 21.

Try the below ugly expression 🙂

if(sum({<[Fiscal Date]={'>=$(=MonthStart(Max([Fiscal Date]), -12))<=$(=MonthEnd(Max([Fiscal Date])))'}>}[Profit])<0,

Count(Aggr(Count(DISTINCT{< Profit={'<0'},

  [Fiscal Date]={">=$(=MonthStart(Max([Fiscal Date]), -12))<=$(=MonthEnd(Max([Fiscal Date])))"}>}

  [Customer ID]),[Fiscal Month],[Fiscal Year],[Customer ID] )),

    Count(DISTINCT{<[Fiscal Date]={'>=$(=MonthStart(Max([Fiscal Date]), -12))<=$(=MonthEnd(Max([Fiscal Date])))'},

    [Customer ID]={"=sum(Profit)<0"}>} [Customer ID]))

[keitel2015]

Hi,

again thanks.

Both formula aren´t correct. For Jun 2015 I should get 9 (Current Period),13 (Rolling 3), 15 (Rolling 6) and 13 for Rolling 12. So neither 9 nor 21 are right, it should be 13. I prepared an excel file which contains all correct values. There must be a way to get the same results in QV.