14 Replies Latest reply on Jun 26, 2018 7:15 AM by Mavis Liu

# Cumlative Sum YoY MoM in one worksheet

HIi Team,

Attached book is for you reference.

I have two data sets (Cost and revenue) and i am trying to calculate below 3 things:

1. Cost income ratio ( This is YTD)

2. CIR comparison with last year : YoY

3. CIR comparision with previous month:  MoM

I have tried using running sum to arrive at YTD but then i am unable to do 2 and 3 points

If i restrict my view at year level then 1 point can be acheived easily but again for comparision values are returning wrong.

Regards,

Deepika

• ###### 1. Re: Cumlative Sum YoY MoM in one worksheet

Hi Deepika,

Please could you provide us with the packaged workbook twbx. The TWB you've provided does not embed the data so we are unable to use it.

Thanks,

Mavis

• ###### 2. Re: Cumlative Sum YoY MoM in one worksheet

Hi,

Have update twb with Twbx

Thanks

• ###### 3. Re: Cumlative Sum YoY MoM in one worksheet

Have you tried to blend the data with Brach code as well.

• ###### 4. Re: Cumlative Sum YoY MoM in one worksheet

Data blending is not an issue.

Problem here is measure is division of cost and income and that is gets measured always at YTD level.

For e.g. if i am selecting month as April then also Cost and revenue would get divided based on YTD numbers ( Jan till Apri)

and then i have to calculate YoY and MoM comparison

This is i am unable to figure out

Regards,
Deepika

• ###### 5. Re: Cumlative Sum YoY MoM in one worksheet

Hi Deepika,

Please see attached workbook, is this the result?

1) First I removed the date filter you had in the filters shelf.

2) I created 3 calculations:

YTD

Calculate pane across:

MoM:

Calculated table across:

Finally YoY:

Calculate this by year of date:

Bring these into the view and add it into your text label.

Then create an adhoc calculation by double clicking in the rows shelf and type in last()=0:

Then drag this field into filters and set it to true.

Thanks,

Mavis

• ###### 6. Re: Cumlative Sum YoY MoM in one worksheet

Hi,

I have checked your workbook, but calculation doesn't seems to be working.

If you notice, Cost income ratio YTD is returning wrong numbers and also if we are doing YoY comparision then April 18 should compare with Apr 17.

Regards,
Deepika

• ###### 7. Re: Cumlative Sum YoY MoM in one worksheet

Hi Deepika,

April 2017 value was: 15.3560%

April 2018 value is: 22.0935%

I work out the YoY % difference as 43.88%.

So please can you tell me why the calculation isn't working? Can you tell me what the calculation should be doing instead?

Right click on the calculation and select edit to update the calculation as you require:

Thanks,

Mavis

• ###### 8. Re: Cumlative Sum YoY MoM in one worksheet

Level 4 cost (YTD): 679

Level 4 revenue  (YTD): 5371

Hence, Cost income ratio should be: 13 %  ( 679/5371) whereas it is showing 51.50%

Sorry! i am not able to figure out calculation.

Would be glad if you can clear my doubts.

• ###### 9. Re: Cumlative Sum YoY MoM in one worksheet

I think i am able to figure out issue here.

In your calculated measures, it's saying results are computed along year of date wheras for me it's saying table across. I have changed in the data pane in table calculation but still it's not showing the one which is there in your screenshot

• ###### 10. Re: Cumlative Sum YoY MoM in one worksheet

Hi Deepika,

Thanks,

Mavis

• ###### 11. Re: Cumlative Sum YoY MoM in one worksheet

Hi,

Level 4 cost (YTD): 679

Level 4 revenue  (YTD): 5371

Hence, Cost income ratio should be: 13 %  ( 679/5371) whereas it is showing 51.50%

Thanks!

• ###### 12. Re: Cumlative Sum YoY MoM in one worksheet

Hi Deepika,

You said 51.50%, which is CostIncomeRatio YTD.

This is because the CostIncomeRatio  YTD is a running sum:

You need to change this to your above equation of Cost YTD/Revenue YTD.

Thanks,

Mavis

• ###### 13. Re: Cumlative Sum YoY MoM in one worksheet

Thanks a lot!!

It worked!!

• ###### 14. Re: Cumlative Sum YoY MoM in one worksheet

Fantastic