nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Prior year value for selected period

MadhabikaM

Hi All,

I needed a logic to pick the values as below:

If my current month selected is Jan, Feb or March FY20 then populate the value from Oct , Nov and Dec FY19 respectively.

Thanks & Regards

Madhu

Accepted answers

Misbah

@MadhabikaM

So what you are saying is that

If Jan 2020 is selected you wish to check Oct 19, Nov 19 and Dec 19 Values

If Feb 2020 is selected you would want to get Nov 19, Dec 19 and Jan 20 Values

And if both Jan 2020 and Feb 2020 are selected then you would want Oct 19 to Jan 2020 selected. If all the above assumptions are true then see if this helps

OFFSET(Check, 3, FALSE) OR OFFSET(Check, 2, FALSE) OR OFFSET(Check, 1, FALSE)

All comments

JaredDolich

@MadhabikaM

Excellent question. One that is asked regularly because there are many solutions.

Keep in mind the "9" you see here can be a line item so you can change that logic dynamically.

For me, I prefer to use OFFSET since that is what you are trying to do. "I want to offset the sales by 9 months". this follows the PLANS methodology.

But as you can see all these will work.

You can also check out this article that discusses the performance impact of each of these. In this case, however, you won't be able to use PREVIOUS. And I don't ever recommend using SELECT.

https://community.anaplan.com/t5/Anaplan-Platform/Performance-Comparison-OFFSET-LAG-PREVIOUS-LOOKUP/td-p/51140

MadhabikaM

Hi Jared,

The above functions only work for Number formatted line item.

But I need a boolean check line item.

when If my selected month parent is quarter 1 (say Q1 FY20),
then enable true for (Oct, Nov, Dec) of Prior year Q4 FY19.

I also tried using a Month(start()) which will give me number 1-12 for respective months and I can use If else condition for enabling true if number >=10 and <= 12(lookup Prior Year), but this is not helping as my model setting is using week grouping 5-4-4.

any other function I can use in this scenarion.

Thanks
Madhu

Misbah

@MadhabikaM

Whatever Professor @JaredDolich said is absolutely right and is applicable for Booleans as well. Instead of writing

OFFSET(Sales, -3,0) which will be applicable for number formatted line items you can write OFFSET(Sales, -3, FALSE).

MadhabikaM

Hi @Mishab,

Offset will not help me here, as I need prior 3 months to be selected not just third month and also , only If my current month selected is in Q1 then check true for prior 3 months.

Thanks
Madhu

Misbah

@MadhabikaM

So what you are saying is that

If Jan 2020 is selected you wish to check Oct 19, Nov 19 and Dec 19 Values

If Feb 2020 is selected you would want to get Nov 19, Dec 19 and Jan 20 Values

And if both Jan 2020 and Feb 2020 are selected then you would want Oct 19 to Jan 2020 selected. If all the above assumptions are true then see if this helps

OFFSET(Check, 3, FALSE) OR OFFSET(Check, 2, FALSE) OR OFFSET(Check, 1, FALSE)

MadhabikaM

Thanks Mishab,

This helps, but the logic need to be update little.
I have done the same and its working fine.

Misbah

@MadhabikaM

Glad that it worked. And the name is Misbah.

Cheers!!

Kanishq17

Hi @MadhabikaM

Alternatively, you could have used movingsum with any aggregation method

PFA the snip,

Hope it helps

Quick Links

Can you help?

Unanswered questions

Are we allowed to create a practice dataset to simulate a real model build (Talent Builder Program)
Done with the course but want to practice a bit more before I take the Level 1 exam
Anaplan Analyst performance on large, sparse models
Has anyone tested Analyst’s response quality/speed on large, sparse models (think 10+ dimensions, millions of line items)? Wondering if there’s a practical ceiling before it starts timing out.
Expand or collapse grid rows: how to publish level 1 and 2 in a 5-level hierarchy
I have a question about the new Expand or collapse grid rows option. Is it possible to publish a grid containing a 5-level hierarchy so that all items from levels 1 and 2 are always visible? And I do not want to use a filter for this.