Skip to main content

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 IBY 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

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
  rBY SUM: Month.Quarter] // Aggregate the data on the quarter
  bBY CONSTANT: Month.Quarter] // Allocate the quarter aggregated data on each Month in the Quarter
    rSELECT: Month - 1] // Shift One month afterwards

 

 

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

Bonus
  oBY SUM: Month.Quarter] // Aggregate the data on the quarter
  aBY CONSTANT: MonthOFILTER: Month.Quarter <> (Month + 1).Quarter].Quarter]
  aSELECT: 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!

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

 

Steven

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]

 

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

 

Steven

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.

Thanks, very useful!

This helped me a lot today! Thank you!!

 

Reply


Ask your question here!

Terms & ConditionsCookie settings