Retrieving last non-zero value

Hi,

I created a module with exchange rates data for each month. Then, I put summary as : sum, time: closing balance.

But as I only have data until Sep 2020, it puts 0 for FY20 and 21. So, I would like to set up a formula that retrieve the last non-zero value for FY20 and FY21 for instance.

Thank you for your help!

Accepted answers

Misbah

@kenzael

Unfortunately there is no LASTNONZERO function available, we will go for a workaround here. See if this helps

Step 1: Create a line item call it Numerator. Write below formula in it

IF 'Exch Rate - Test' <> 0 THEN 'Exch Rate - Test' ELSE PREVIOUS(Numerator) and change the summary to Sum, Closing Bal

Step 2: Create another line item call it Denominator.

Hardcode number 1 in it and change the summary to Average

Step 3: Go to your target line item ( in my case Exch Rate - Test) and change the summary from Closing Balance to Ratio (Numerator/Denominator)

All comments

Misbah

@kenzael

Unfortunately there is no LASTNONZERO function available, we will go for a workaround here. See if this helps

Step 1: Create a line item call it Numerator. Write below formula in it

IF 'Exch Rate - Test' <> 0 THEN 'Exch Rate - Test' ELSE PREVIOUS(Numerator) and change the summary to Sum, Closing Bal

Step 2: Create another line item call it Denominator.

Hardcode number 1 in it and change the summary to Average

Step 3: Go to your target line item ( in my case Exch Rate - Test) and change the summary from Closing Balance to Ratio (Numerator/Denominator)

ABHI3795

IF Source_Line_Item <> 0 THEN Source_Line_Item ELSE PREVIOUS(Target_Line_Item)

Quick Links

Can you help?

Unanswered questions

Action to copy data from one version to another version only for a specific module
Ideally, I would like to setup an action that can copy data from one version to another only for a specific module. For every new version I create, I import new data into certain modules and then I manually copy data over for a specific module. I am wondering if I can just setup an action to select an origin version,…
Where can I find export file after running export process using machine task in a workflow?
I am testing export actions using workflow but its not generating any file when I am running the workflow, but the same process is generating export file when ran from the Actions→Process section. Is their any issue with the workflow functionality? Also, If I want to generate a file after completion of workflow and pull…
Automating Flat File Uploads to Anaplan Data Orchestrator (ADO) – Best Practices and Methods
Hello Anaplan Community, I am seeking guidance on the most effective ways to automatically upload flat files to Anaplan Data Orchestrator (ADO). Currently, our process involves loading flat files to an Anaplan Datahub using Anaplan APIs and custom Python scripting. With Anaplan Data Orchestrator's capabilities for data…