Cumulative Rate Changes

Dan_Sharman
4 yrs ago
16replies
Answered

Hi all,

I am trying to replicate the example seen in the picture. As you can see, with each different year the rate % changes. I want to be able to track the cumulative change from a given year onwards. So start at 100% and then + each years rate change (creating a cumulative amount) and then use this as the new base to add next years rate change % to. Is there a way of doing this in Pyramid?

Thanks,

Dan

- Senior Director of Product
- Ian_Macdonald
- 4 yrs ago
- Reported - view
Hi Dan,

I have some good news and some (slightly) bad news...

The good news is:

The (slightly) bad news, or, depending on how you see it, more good news, is that this requires Pyramid 2020.10.xxx in order to work.

The reason for this is that the cumulative calculation relies on the upgraded version of the PQL CurrentMember function available in Pyramid 2020.10. Great reason to upgrade as soon as possible though when this gets released in a week or so.

I created a simple model using the data you provided in your example spreadsheet.

Then I built a Rate Change from Base calculation in formulate thus:

The multiplication by -1 is to get the sign right for the Rate Change direction and subsequent cumulative process.

Then I built a Cumulative Rate Change thus:

I.e. Starting at the base, for each year add up the Rate Change from Base from 2013 to the current year (Year, CurrentMember block).

In the current version, 2020.05, CurrentMember only returns a value, not the member name, thus cannot be used as part of the RANGE function, as the RANGE function expects a member name for the start and end of the range.

2020.10 enhances CurrentMember() to return the Member name where required. This greatly enhances the calculation capability, especially for this type of dynamic range calculations defined in the Measure dimension.

Keep an eye out for the release announcement, best done by following the Product Announcement forum.

Hope that helps.

Ian
- Senior Director of Product
- Ian_Macdonald
- 4 yrs ago
- Reported - view
BTW - Your cumulative (compound) rate change numbers don't look right. My numbers match exactly the numbers generated by Excel when I prototyped the calculations to make sure I understood them properly.

For example, in your numbers for 2016, it shows a -5.9% change from base. Subtracting that from the previous cumulative value, 76.86% gives 70.96% not 72.34%.

Unless, of course, I've misunderstood your algorithm. However, that doesn't really impact the core approach.

Ian
- Dan_Sharman
- 4 yrs ago
- Reported - view
Hi Ian, that's great! Thanks so much for getting back to me. Will give this a go when released - and thanks for the heads up re the calc, probably a cell that wasn't copied down or something but good to see it can be done in the upcoming version!
- Senior Director of Product
- Ian_Macdonald
- 4 yrs ago
- Reported - view
Hi Dan,

Re-examining your grid I now better understand the algorithm you are using to calculate your compound rate change column. Here's the result, which matches your Excel grid:

The revised compound rate change calculation is not a simple cumulative total as I previously thought and is rather more involved, but is still achievable.

Regards,

Ian
- Dan_Sharman
  
  4 yrs ago
  
  Reported - view
  Ian Macdonald Hi Ian, Sorry I have only just seen this, if you can remember, was this revised calculation much different to the one you specified originally?
  
  Many thanks
  
  Dan
- Senior Director of Product
- Ian_Macdonald
- 4 yrs ago
- Reported - view
Err... Yes. The revised calculation takes into account the fact that it is not a simple cumulative total, but that the result the calculation for one row feeds into the calculation for the next row. It's a pseudo recursive calculation.

Unfortunately if you try and code this in PQL it gives a circular reference error. Each term then has to be specified explicitly, which makes the calculation expression huge when you get past 2 or 3 rows. It works, but is very unwieldy.

LMK if you'd like me to share it with you through email.

Cheers,

Ian
- Dan_Sharman
  
  4 yrs ago
  
  Reported - view
  Ian Macdonald Thanks Ian, it would be great if you could share via email if not too much trouble?
  
  dsharman@aegislondon.co.uk
  
  Thanks again,
  
  Dan
- Senior Director of Product
- Ian_Macdonald
- 4 yrs ago
- Reported - view
OK. I'll DM you to set up a call.

Ian
- Malavika_Nair
- 1 yr ago
- Reported - view
Hi, we are attempting a similar use case for Pyramid. We have a data source with data at the daily level for return %, and we want to calculate cumulative aggregate return % based on what rows are available which will be controlled by a date slicer (so we cannot pre calculate the value in the model, it must be done in formulate / discover). While we see an aggregate SUM function, there is no similar aggregate PRODUCT function which is the excel functionality we are trying to mimic (product of all of the values in a single column). How would we implement this functionality within Pyramid?
- Customer Solutions Architect
  
  Moshe_Yossef
  
  1 yr ago
  
  Reported - view
  I assume Ian will also address this, however I don't understand how can the return% aggregate as a product?
  If I have one row in the data where the return % is 5%, and a second row where it is 2%, I am pretty sure the aggregation will eventually be a number between 2% and 5%, I strongly doubt it would be 2% * 5% = 0.1%.
  What does return% actually represent? is it a division between 2 columns in the data?
- Malavika_Nair
- 1 yr ago
- Reported - view
Here is the excel functionality we want to replicate:

We want to take the product of 1+ each daily return % and then subtract 1 from that cumulative product. This will represent the cumulative aggregate return % across the time selected (which is the month of September 2023 in this example).
- Senior Director of Product
  
  Ian_Macdonald
  
  1 yr ago
  
  Reported - view
  Hi Malavika,
  
  Currently this is not possible in Pyramid, as it requires a pseudo recursive expression, i.e. the results of the first expression are fed into the next expression etc. Trying to implement this in PQL will result in a circular reference error.
  
  However, we are adding a new PQL Function:
  
  multiply(<set>, <numeric_expression>)
  
  similar to the Average, Maximum, Minimum functions.
  
  Multiply will take a list of values and output the product, exactly what you require.
  
  We hope to have this function available in the next release of Pyramid.
  
  Hope that helps.
  
  Ian
- Malavika_Nair
- 1 yr ago
- Reported - view
Thank you for the update Ian, that function sounds like exactly what we need. Is there a timeline in mind for the next Pyramid release? Please let me know if this feature does get included in the next release.
- Senior Director of Product
- Ian_Macdonald
- 1 yr ago
- Reported - view
Hi Malavika,

Yes, it is planned for the next release, which hopefully will be with you in early December.

Ian
- Malavika_Nair
- 1 yr ago
- Reported - view
Awesome - thank you!
- "making the sophisticated simple"
- AviPerez
- 1 yr ago
- Reported - view
While there are several alternatives to address the requirement, a new specific PQL function - "Multiply" - has been implemented in our latest release Pyramid 2023.10 to solve this question directly.

Please find full release details here and a list of all implemented forum ideas here. Thank you for your participation and support.