Filter formular Monthvalue

Highlighted
Contributor

Filter formular Monthvalue

hi,

 

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

 

thanks

 

Donna

8 REPLIES 8
Highlighted
Super Contributor

Re: Filter formular Monthvalue

Hi @Donna Marie 

 

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

Highlighted
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

Highlighted
Super Contributor

Re: Filter formula Monthvalue

Hi @Donna Marie 

 

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

Screenshot_206.png

Screenshot_207.png

 

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

Screenshot_208.png

Screenshot_209.png

 

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

 

For more detail, please go through the below link:

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

 

Hope this helps!

Thanks

Akhtar

Highlighted
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

Highlighted
Super Contributor

Re: Filter formula Monthvalue

Hi @Donna Marie 

 

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

Highlighted
Super Contributor

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
Highlighted
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

Highlighted
Super Contributor

Re: Filter formula Monthvalue

Hi @Donna Marie,

 

Question 1:

 

Capture.JPG

 

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

Capture2.JPG

 

Answer:

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