how do I construct a formula using monthvalue to be used as a filter ?
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.
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
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.
For more detail, please go through the below link:
Hope this helps!
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
You are very close now.
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.
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.
Formula for LI in Target Module is Monthvalue(Source Module.LI)
Week 1 False
Week 2 False
Week 3 False
Week 4 False
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
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
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
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.
Sorna Raja Prabhu