+2I have a table with Month and a YTD calculated item in the columns and five metrics in the rows. Four of the metrics are financial. The fifth is headcount.
As my YTD calculation applies across all metrics, my heads are adding up rather than showing the last value. Note that I changed the settings of my underlying heads metric to Last rather than Sum for time dimensions, but this didn’t make any difference.
My questions is, do calculated columns have to apply across all metrics in the table?
Best answer by Veniamin
Hi John,
No, they don't have to. In a Table you can configure a Calculated Item differently per Metric. Right-click the cell where your YTD column meets the headcount row, pick "Edit Calculation for this Metric", and change the calculation type just for that one. The four financial rows keep cumulating, headcount does its own thing.
It's in the docs under "Editing a Calculation for a Specific Metric": https://kb.pigment.com/docs/compare-data-calculated-items
On why the aggregator change did nothing, there are two separate reasons and neither is a bug.
The Year to Date preset is a Cumulate calculation. Cumulate adds items up sequentially along the Dimension it sits on, and that's the whole of what it does. It never consults the Metric's default aggregator, so setting headcount to Last has no effect on it.
Also, default aggregators only come into play in new Views, and when the Dimension is in Pages or hidden. Your Month is in columns and visible, so the aggregator isn't being asked anything at all. Changing it on a View that already exists wouldn't surface either.
For headcount you presumably want the closing value rather than a running total, so in the per-Metric edit turn Cumulate off and point the calculation at the single month you want. If you'd rather it follow your actuals month instead of being pinned to a fixed one, an Item Variable does that, and members can move it in their own session without editing the configuration.
Calculated Items can't be referenced by other Metrics. If that YTD number has to feed something downstream, use the YEARTODATE function in a Metric instead.
Hi John,
No, they don't have to. In a Table you can configure a Calculated Item differently per Metric. Right-click the cell where your YTD column meets the headcount row, pick "Edit Calculation for this Metric", and change the calculation type just for that one. The four financial rows keep cumulating, headcount does its own thing.
It's in the docs under "Editing a Calculation for a Specific Metric": https://kb.pigment.com/docs/compare-data-calculated-items
On why the aggregator change did nothing, there are two separate reasons and neither is a bug.
The Year to Date preset is a Cumulate calculation. Cumulate adds items up sequentially along the Dimension it sits on, and that's the whole of what it does. It never consults the Metric's default aggregator, so setting headcount to Last has no effect on it.
Also, default aggregators only come into play in new Views, and when the Dimension is in Pages or hidden. Your Month is in columns and visible, so the aggregator isn't being asked anything at all. Changing it on a View that already exists wouldn't surface either.
For headcount you presumably want the closing value rather than a running total, so in the per-Metric edit turn Cumulate off and point the calculation at the single month you want. If you'd rather it follow your actuals month instead of being pinned to a fixed one, an Item Variable does that, and members can move it in their own session without editing the configuration.
Calculated Items can't be referenced by other Metrics. If that YTD number has to feed something downstream, use the YEARTODATE function in a Metric instead.
+2Thanks so much!
(Setting YTD / Heads as ‘no calculation’ looks good 👍).
No account yet? Create an account
Single Sign-On Need help?
Log in with your Pigment accountEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
