Skip to main content

I have a Workday load with multiple loads per month and I want to be able to pull into a metric, the individual fields by load date and by scenario. I have two scenarios with one set to Feb 2 and one set to Feb 15, when I test the following logic: EE_Load_Workday_Actuals.Departmentnby lastnonblank: EE_Load_Workday_Actuals.'Workday Employee', TIMEDIM(EE_Load_Workday_Actuals.'Load Date',Month)])by lastnonblank: timedim('Set_Last Actuals WD Load Date',Month)]  , the metric seems to just pull the lastnonblank literally from the transaction list. I changed and moved a sample in-month change to department for one record and it just took whichever record was physically lower down in the transaction list, rather than the load date.

Is there a better way to go about this without the timedim and lastnonblank functions and modifiers? I couldn’t find anything else in the Community and couldn’t think through another approach. If there is a more efficient way to use a date within a modifier to get Pigment to return changes-in month, please let me know!

For context, the Set Last Actuals WD metric looks like this: 

 

Hi ​@AndrewRabines 

You could use the 'MAXOF' function to return the latest date from a block. However, this function does not consider other dimensions like 'Scenario'.

 

MAXOF' function: Returns the highest value of a Block.

 

Syntax

MAXOF(Block)

 

Arguments

Argument Type Dimensions Description
Block

(required)

 Integer, Number or Date Any If Block is a Date, the function returns the latest date. For Number or Integer, it will return the highest value.

 

Returns

Type Dimensions
Same Type as argument Block No Dimension

 

Examples

Employee is a Dimension List:

Formula Result Description
MAXOF(Employee.ID)  3

3 is the highest number from this block.
MAXOF(Employee.'Start date') 12/14/2024

Returns the latest start date from the block.

 

This result you’re trying to achieve may need to a more complex formula or use a combination of functions to achieve the desired result. Here are some articles you can have a look at:

How to use the Formula Wizard to master the BY Modifier (https://community.pigment.com/modeling-formulas-85/how-to-use-the-formula-wizard-to-master-the-by-modifier-1273),
STARTOFMONTH function (https://community.pigment.com/functions-84/startofmonth-function-267),
MAXOF function (https://community.pigment.com/functions-84/maxof-function-150),
Aggregating data in formulas (https://community.pigment.com/modeling-formulas-85/aggregating-data-in-formulas-179)

Let us know if this helps or not, thanks!

Hi ​@AndrewRabines ,

You could use the ON parameter to order the TL list based on the load date. So your formula would be something like kREMOVE LASTNONBLANK ON LOAD DATE: xxx]

Hi all, 

 

I actually solved this by utilizing the week calendar dimension and then taking the lastnonblank week converted to month. This is working as I had hoped. 

 

Thanks for the suggestions!

Reply


Ask your question here!

Terms & ConditionsCookie settings