Solved

Challenges with Calculated Items & Aggregators with YoY Calculations

  • 4 November 2022
  • 9 replies
  • 261 views
J
Rank
Userlevel 2
Badge +1

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?

icon

Best answer by Stef 23 December 2022, 16:02

View original

9 replies

Nathan
Rank
Userlevel 6
Badge +10

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

 

J
Rank
Userlevel 2
Badge +1

@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.  

 

 

 

Nathan
Rank
Userlevel 6
Badge +10

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

 

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

J
Rank
Userlevel 2
Badge +1

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

J
Rank
Userlevel 2
Badge +1

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

A

@Justin Groover as I understand it you and Nathan touched based offline. However is there is any outstanding on this please let us know!

J
Rank
Userlevel 2
Badge +1

@Tess.Hunt Nathan and I did not touch base offline and the issue still persists as originally documented.  Thank you for following up and please advise if I can provide any more information for next steps.

Stef
Rank
Userlevel 3
Badge +2

Hi @Justin Groover ,

As your Gross Margin % is not a separate metric but calculated with “Show values as” I don't have a solution right away but have to think about another way of doing it. 

Maybe the best way is to submit this as a support ticket to get private help on this. 

Kind Regards, Stef
S
Rank

Hey @Justin Groover . Regarding point 2, I think you would have the same issue like me with calculating attrition rate where I had incorrect summary on the rollups. I have solved this by doing the calculation inside the table itself.

 

So attrition rate is calculated: Terminations Current Month / Headcount Current Month

 

I have brought in both “Terminations Current Month” and “Headcount Current Month” metrics into the table. 

On the “Terminations Current Month” metric I went and changed it to a calculation (row,field,item or whatever it is called...).

 

Metric which I chose was “Headcount current Month”.
So this way I practically did a desired formula Terminations Current Month / Headcount Current Month in a table calculated , what I would usually do in a metric, but then rollup % would be wrong.

This way table calculation takes summary from the rollups and also does division on the rollups, which is what we need.
Afterwards I have hidden the “Headcount current Month” inside the table, which left we with only desired metric.

 

Hope this helps

Reply


Ask your question here!

Terms & ConditionsCookie settings