Limit the resulting value to a specific File Type

kmht0307

I have data organised by File Type, so has values such as:

Month End

Flash 1

Flash 2

Flash 3

Flash 4

Flash 5

For Cohort 202602 (Feb 26), the module holds data for Flash 1, Flash 2 and Flash 3. I only want to bring in the data for the latest Flash version - this is shown in the Module in the line item called File Type pulled in from another module i.e. Flash 3 so 9,426,366.

How to I reference this in the line item formula in Relevant Tariff CoS?

At the moment the data is being brought in for Flash 1, Flash 2 and Flash 3 as you can see in the drilldown.

File Type is held in a module called File Type Selection here:

Accepted answers

NoahJ

@kmht0307 First off yes that existing module looks perfect! But hmmm strange about the final results… I see that the Relevant Tariff CoS line item is on a subsidiary view, what are its dimensions?

My theory based on the screenshots are that File Type is a dimension on that line. When that is the case, since both the source data and the calculation line are on the same dimension, it kind of ignores the lookup… which is kind of strange, I did not expect that and had to recreate it in a test model to confirm. But normally lookups are used to account for source data being on a different dimension than the calculation, not for when that dimension is already present.

So if the Relevant Tariff CoS is in fact on File Type, you could look into removing that dimension from the line item, which now since you have that Lookup it should correctly show just the value on the File Type that is selected for that month. However, if there are other downstream line items that reference this one, then removing the dimension would likely change how those function.

In that case (File Type is on Relevant Tariff CoS, and it has downstream dependencies that break if you remove the File Type dimension) you could either:

Create a new line item without File Type dimension and with the Lookup, to be displayed instead, or
Take a different approach - you could add logic into the line item that says "IF SYS by File Types.Item = SYS Time Settings by Month.Selected File Type, THEN <values> ELSE 0". The "SYS by File Types.Item" would just be something on the File Types dimension with the formula of ITEM(File Types). This would force all rows in that drill down you are doing to be 0 other than the one for the correct File Type.

Hopefully all of that makes sense… definitely feels like going down the rabbit hole!

All comments

NoahJ

Hey there, I think I could help! Because you have that "File Type Selection" data it becomes much easier - without it, you could do a sort of "last non-zero" calculation but that becomes more difficult.

First off, it appears that the "File Type Selection" is on a Fake Time dimension, not the native Anaplan time - since the column headers are 202509, 202510 etc., instead of Sep 25, Oct 25, etc.. You will want to pull that over to a native time module. I'm not sure if you already have a "Real to Fake Month Mapping" or something like it, it would just be a line item in the native time dimension that is formatted as the fake time list:

From there, you could pull that "Selected File Type" over to the native time dimension:

And then I'm not sure what the exact dimensions of your Relevant Tariff CoS are, but I beleive you should be able to add LOOKUP:SYS Time Settings.Selected File Type (or whatever module / line item name you choose) to the formula, which will cause it to look at that file type only for the given month.

Hopefully that helps!! Let me know if you have any other questions!!

kmht0307

@NoahJ Thank you for the reply to my question, I can see where you are going with this.

What I have already is this module that converts the Month to the fake time, so can this be adapted to provide the solution?

So I have now added in the Selected File Type here:

And referenced this by LOOKUP in the Blank Tariff line item:

However, the value pulled through for Feb 26 is still for Flash 1, Flash 2 and Flash 3 as per the drilldown below:

NoahJ

@kmht0307 First off yes that existing module looks perfect! But hmmm strange about the final results… I see that the Relevant Tariff CoS line item is on a subsidiary view, what are its dimensions?

In that case (File Type is on Relevant Tariff CoS, and it has downstream dependencies that break if you remove the File Type dimension) you could either:

Create a new line item without File Type dimension and with the Lookup, to be displayed instead, or
Take a different approach - you could add logic into the line item that says "IF SYS by File Types.Item = SYS Time Settings by Month.Selected File Type, THEN <values> ELSE 0". The "SYS by File Types.Item" would just be something on the File Types dimension with the formula of ITEM(File Types). This would force all rows in that drill down you are doing to be 0 other than the one for the correct File Type.

Hopefully all of that makes sense… definitely feels like going down the rabbit hole!

Quick Links

Can you help?

Unanswered questions

Workflow does not display detailed error messages and marks success despite partial failures
Summary: Workflow does not display detailed error messages and marks success despite partial failures. Impact: Developers and workflow users will be receiving false-positives due to the “Completed Message” despite partial failures, that are present when manually triggering processes and through cloudworks. The expectation…
Update Anaplan IRR Function to calculate running IRR
We have a business requirement of calculating running IRR to calculate the performance fee. Currently, Anaplan IRR function gives 1 final output basis entire cashflow We need running IRR to start calculating the performance fee from the period if the IRR exceeds Hurdle rate This is a highest priority item and needs to get…
A reminder of Bulk Copy - Current year to Prior year version for YoY analysis
A quick reminder of the Bulk Copy functionality. Bulk Copy allows you copy large volumes of data from one slice of a model to another in a single, optimised operation, instead of using formulas or imports. Use case: copy a version (RF1) into a prior year version (PY RF1) using a versions list to allow for year-on-year…