Question

Challenges with Calculated Items & Aggregators with YoY Calculations

  • 4 November 2022
  • 5 replies
  • 72 views

Badge

I am trying to create multi year P&L views, with months in the columns.  After the last month of the year, I want to show the annual total, YoY $ value, and YoY % change. (Months->Annual Total->YoY $ change->YoY % change).  

The challenges I’m encountering are as follows - example with numbered notes shown in snippet below:

  1. Inability to choose position of aggregators.  Pros of using aggregators vs calculated items are that gross margin % calcs roll up correctly, and the values are retained when I collapse the months and only want to show the annual total.  
  2.  Gross margin % calculated items roll up incorrectly.  It displays the sum of the months’ percentages, rather than the annual gross profit value divided by the annual revenue value.  Pro of calculated item is ability to choose column positioning. 
  3. Is there any way to hide a value in a calculated item?  The YoY % calculation displays a nonsensical value when calculating against % margin values.  Only workaround I’ve currently identified is formatting the individual cell with white text and background to effectively be blank, but this doesn’t scale well as the formatting doesn’t apply year over year and the value still exists in excel exports..

  
Are there any workarounds or approaches that I’m not considering here?


5 replies

Userlevel 5
Badge +4

Hi Justin,

You can edit the “formula” of a calculated at the cell level. For example to hide the YoY%, right click on the cell > Edit this calculation > No calculation should remove it

You should also be able to do a specific calc for your Gross Margin % doing this

 

Badge

@Nathan , thank you.  It looks like I can use this to hide the YoY % value.  Ideally, I’d be able to show the YoY change in %, but I don’t see how I could do that.  For example, if this year’s gross margin % was 72 and last year’s was 70, I’d like to display a value of 2%.

I’m also not seeing any calculation options for the gross margin % that would work.  How would you approach this?  This is one of the most typical column views (Months->Annual Total->YoY $ change->YoY % change) I’ve seen in reports so surely a solution to this has been identified before.  

 

 

 

Userlevel 5
Badge +4

Sorry, “Edit calculation for this Metric” will do it.

 

If you select the application variables at the cell level, it will work yes

Badge

Thanks @Nathan.  Do you have a recommendation for how to make the gross margin % calc work?

Badge

Hi @Nathan , curious of you have any thoughts here.  Let me know if I’m missing something.  

Reply