Filter formular Monthvalue

Donna Marie
Donna Marie
in Modeling

hi,

 

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

 

thanks

 

Donna

Tagged:

Answers

  • Akhtar.shahbaz
    Akhtar.shahbaz

    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

  • Donna Marie
    Donna Marie

    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

  • Akhtar.shahbaz
    Akhtar.shahbaz

    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

  • Donna Marie
    Donna Marie

    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

  • Akhtar.shahbaz
    Akhtar.shahbaz

    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

  • prabhu
    prabhu

    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

  • Donna Marie
    Donna Marie

    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

  • prabhu
    prabhu

    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

Categories