Skip to main content

I am trying to recreate the below waterfall-type allocation and running into an issue having to use previous() functions across multiple dimensions.

 

I created an example below to recreate what I am trying to accomplish. In the example, I have a certain amount of dollars to spend each month that I need to allocate across various items. I can’t spend more than the amounts on the column header and each item has a certain cost associated with it. 

 

I am able to accomplish it with the formula using excel below but when I try to do it in Pigment, I having to use previous on both the month dimension and the item dimension in order to ensure I don’t go over the capped quantity/cost.

 

Anyone have any suggestions?

 

 

 

Hi ​@Shane Crines ,

We should be able to achieve this without PREVIOUS but with 2 CUMULATE instead.
Can you try the following :

  • Create 2 metrics for CumulativeDollars and CumulativeCapacity as follow dimentionalized by Month and Items:
CUMULATE(Capacity,Items)tADD:MONTH]
CUMULATE(Dollars,Month)oADD:ITEMS]
  • Then create the following metrics :
    ​​​​Staging1 : 
    MIN(
        CumulativeCapacity, 
        CumulativeDollars
      )

    Staging2 :

    Staging1-Staging1iSELECT:Month-1]

    And finally 
    Output :
     

    Staging2-Staging2aSELECT:Items-1]

 

Here is a view of all the metrics, note that the 2 first ones don’t need to be created and Staging1 can be replaced by their formulas.

 

Hope this helps,

Reply


Ask your question here!

Terms & ConditionsCookie settings