Solved

Cumulate amount for a quarter and keep it only on the last month of the quarter

  • 16 November 2022
  • 6 replies
  • 48 views

Userlevel 1

Hello Pigment Community,

 

I am trying to calculate the quarterly bonus of my employees and shift it by one month to simulate the payout on the first month after the quarter end.

I will detail below the steps of my calculation and where I am stuck:

  1. My bonus expenses are by month. I was able to sum the quarterly amount with a [BY SUM: Month.Quarter] modifier.
  2. I want to add again the “Month” dimension and put this quarterly amount on the last month of the quarter only (or first month of the quarter, it doesn’t matter). This is where I am stuck. If I use a [BY CONSTANT: Month.Quarter] - the amount is written on all months of the quarter, and then I am unable to filter only on the first or last month of the quarter.
  3. The last step will be to SHIFT this amount by 1 month to simulate the payout on the first month after the quarter ends.
     

I would appreciate your help if you already worked on this topic!

 

Thanks in advance,

Steven

icon

Best answer by francois 16 November 2022, 17:44

View original

6 replies

Userlevel 5
Badge +5

Hi Steven,

Here’s what I came up with, which might not be the clearest solution, but it works.

I’ve followed your steps and decided to filter on months by using this formula (basically comparing whether each Months’ quarter is different from the next, which is only true for the last month on each quarter.

Month.Quarter <> (Month + 1).Quarter

I then incorporated this calculation in a filter directly and got this formula.

Bonus
  [BY SUM: Month.Quarter] // Aggregate the data on the quarter
  [BY CONSTANT: Month.Quarter] // Allocate the quarter aggregated data on each Month in the Quarter
  [FILTER: Month.Quarter <> (Month + 1).Quarter] // Keep only values on last month of the quarter
  [SELECT: Month - 1] // Shift One month afterwards

 

 

If you feel daring, this approach is a bit more complex but also more optimized:

Bonus
  [BY SUM: Month.Quarter] // Aggregate the data on the quarter
  [BY CONSTANT: Month[FILTER: Month.Quarter <> (Month + 1).Quarter].Quarter]
  [SELECT: Month - 1] // Shift One month afterwards

Instead of allocating on all months in the quarters, I’ve filtered the Months (using the same rule as above) directly in the allocation key Month.Quarter. This way Pigment only allocates data on the relevant Months.

The formula is a bit less legible though. There’s probably other / cleaner ways to do it, if you think of another solution let me know!

Userlevel 1

Thank you François for the very quick answer, it’s working very well!

 

Steven

Userlevel 5
Badge +5

I was thinking about your formula and also came up with this approach which is similar in some ways but approaches the problem with functions rather than modifiers (at least for part of it).

IF(Month.Quarter <> (Month + 1).Quarter, CUMULATE(Bonus, Month, Month.Quarter))[BY CONSTANT: Month - 1]

 

Userlevel 1

Thank you François, do you know which solution is the best in terms of performance?

 

Steven

Userlevel 5
Badge +5

These should all be equivalent in speed.
Out of the three the second one could be more optimized (slightly smaller cardinality because we don’t allocate on all months), but unless you have millions of employees receiving bonus they should all behave the same as it’s clearly not a complex / exceptionally large calculation.

Userlevel 1

Thanks, very useful!

Reply