Register

Contributor

## Filter formular Monthvalue

hi,

how do I construct a formula using monthvalue to be used as a filter ?

thanks

Donna

8 REPLIES 8
Community Boss

## Re: Filter formular Monthvalue

Can you explain more about your requirement, It doesn't clear to me what do you want to achieve. if possible give example or snapshot then we will help you out.

Thanks

Akhtar

Contributor

## Re: Filter formula Monthvalue

It's for level 2 sprint 3 inv01 module.  I was asked to construct a formula using monthvalue to create a filter in sys01 time settings by weeks

idea is to ensure all weekly Boolean are checked before the month Boolean is checked

Community Boss

## Re: Filter formula Monthvalue

MONTHVALUE is used to convert/spread monthly value into weekly value or vice versa.

1. Monthly value to Weekly

Lets say i would like to spread monthly value (Jan) to weekly which fall under Jan month

2. Similarly if you want sum your weekly value by month, you can use MONTHVALUE function.

Lets say for month Jan we have 4 weeks and  I want to know by month

Similarly you have to think about your use case, it will work for Boolean also.

https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/MONTHVALUE.html

Hope this helps!

Thanks

Akhtar

Contributor

## Re: Filter formula Monthvalue

so if I want to do a formula to execute a filter in sys01 time setting by weeks- do I use monthvalue ('sys02 time setting by months'.'filter')

the 'filter' in the sys02 time settings is built using Boolean and I have entered 'true' to check all boxes and removed true afterwards

will that ensure that all weeks have to be checked first before the month Boolean gets checked

Community Boss

## Re: Filter formula Monthvalue

You are very close now.

the 'filter' in the sys02 time settings is built using Boolean and I have entered 'true' to check all boxes and removed true afterwards

If you entered true then removed it mean that line item is true for all time period, be careful about the requirement whether you need some specific time period or entire.

Just FYI, if you want to mark "True" for whole period then Just click Spacebar on the line item, it will automatically tick for everything. no need to write formula true and then removed it.

Hope this helps!

Thanks

Akhtar

Community Boss

## Re: Filter formula Monthvalue

Hi @Donna Marie,

You are in the right track.

Your question : will that ensure that all weeks have to be checked first before the month Boolean gets checked ?

Answer : No, when the month gets checked(True) First then all the weeks will get checked.

Let us understand how monthvalue() Works in Boolean.

eg:

Source Module

LI

Jan      False

Feb     True

Formula for LI in Target Module is Monthvalue(Source Module.LI)

Target Module

Jan               LI

Week 1    False

Week 2    False

Week 3    False

Week 4    False

Feb

Week 5    True

Week 6    True

Week 7    True

Week 8    True

Target Module is dependent on Source Module

that is, Week 1,2,3,4 are set as false in Target Module because those weeks are dependent on Jan Month in Source Module

Week 5,6,7,8 are set as True in Target Module because those weeks are dependent on Feb Month in Source Module

So When Month Boolean is checked first then the week Boolean gets checked.

Hope it make sense.

Let me know if you din't understand

Sorna Raja Prabhu
Contributor

## Re: Filter formula Monthvalue

if the month set in source is true

will there be situation where in the target system week module, certain weeks of jan are false and certain are true

the situation is to cater when there are certain weeks in jan are true and false as not all weeks may be true but end result is the month will not be checked unless all weeks are checked

if I were to construct in filter line item of sys time setting by weeks using formula  monthvalue ('sys time setting by months'.'filter')

does this cater to above situation

will this target and source module principle still work as you had explained

your example is clear where month is false because all weeks were false

Community Boss

## Re: Filter formula Monthvalue

Hi @Donna Marie,

Question 1:

Answer : NO. If You set Jan Month in Source as True -> in Target module, certain week of Jan won't be False Always.

All Weeks of Jan will be True

Question 2

Passage 1 says -> Weeks of Jan has both possibilities of True and False. End result is month

Here you have changed the dimension of source and target.

in my previous post Source and Target module has Month and Week as Dimension respectively.

But in your passage 1 : Source and Target has Week and Month as Dimension respectively.

Your assumption is correct but don't get confuse between 2 scenario.

Answer for Scenario 2: you don't want to use monthvalue() function instead you can refer directly and change the summary settings for Boolean in source module (ANY, ALL, LASTNONBLANK, FIRSTNONBLANK).

NOTE: in your model you are writing formula in a week dimension module not in Month dimension module.

so my previous post world be helpful.

Thanks,

Sorna Raja Prabhu

Sorna Raja Prabhu