Creating a responsive calculated field for monthly/quarterly percentages in pivot tables

[crizar]

I have a calculated field that correctly shows monthly percentages, but isn’t working as expected for quarterly views.

Current Calculated Field:
sumover_pivot_qty_rec = sum({quantity_unpacked})/sumOver(sum({quantity_unpacked}),[{destination_fc_region}, truncDate(‘MM’, {received_datetime})])

Current Behavior:

• Monthly view (works as intended):
  Example from below screenshot: LP Receive: 1.03M/1.8M = 57.1%
  This correctly shows the percentage of quantity received for LP over the total quantity received that month.
• Quarterly view (needs adjustment):
  Example from below screenshot: LP Receive: 4.46M/21.35M = 20.9%
  This shows the percentage over the total for all quarters, but I need it to be per quarter.

Desired Quarterly Result:
LP Receive: 4.46M/10.46M= 42.6%

How can I modify this calculated field to maintain consistent percentage logic for both monthly AND quarterly view in my pivot table?

Thank you for your help,
Cris

[duncan]

Hello @crizar

How are you switching between monthly and quarterly views? Are you updating your truncDate portion or are you changing the your date fields aggregation?

[crizar]

Added three columns for the same data field. For each of these columns, I’m aggregating them at different time intervals (month, quarter, and year).

[DylanM]

Hello @crizar, I am assuming that the conflict here is between the aggregation type for the row value in comparison to the total/subtotal value. I think if we convert the numerator and denominator into LAC-W aggregations, aggregate the row value as a min, and then update the total to a sum, we should receive the expected values.

The updated calculated field should look something like this:
sumOver({quantity_unpacked}, [{destination_fc_region}, truncDate('MM', {received_datetime}), {Device}], PRE_AGG)/sumOver({quantity_unpacked}, [{destination_fc_region}, truncDate('MM', {received_datetime})], PRE_AGG)

Feel free to add another partition to the numerator if that doesn’t totally match the expected value. That was my best guess when viewing the screenshots you shared below. It will just require partitions for all of the fields you include in the Rows of the pivot table.

Now, when you include this field in your visual, it will default the aggregation to min. That is required to show to proper value on LAC-W aggregations. If you select the 3 dots in the visual field well, you can update the calculation of the total value to a sum instead of min. That should help to display the correct values when you roll up to the full quarter rather than displaying the values month over month. Let me know if that helps!