After a boolean is selected, I need the last month of every quarter that follows to be selected

Olek P
edited January 10 in Modeling

Hi, 

 

Currently, when I select a boolean in my R21 (that is dimensioned by quarters, ss2) I get the last month of the quarter selected in my C76 module(ss). How can I make it so that once I select the boolean, the last month of every quarter that follows that quarter becomes selected as well. For example: I select Q1 2023. March 23, June 23, Sep 23, Dec 23.... gets selected. 

 

Thank you in advance

Tagged:

Best Answer

  • ManjunathKN
    edited November 2022 Answer ✓

    @opolishchuk 

     

    Present Logic, Q2FY21 is selected and i am getting true for Jun 21,sep 21 and dec 21 only out of 2 year time scale.

    This logic - IF NOT MOVINGSUM(Quarter to months, 1, 100, ANY) THEN PARENT(ITEM(Time)) <> NEXT(PARENT(ITEM(Time))) ELSE FALSE give all true for future last months from selected quarter. ex: Q2FY21 is selected and i will get true for Jun 21,sep 21, dec 21,Mar 22..so on until the time period ends.

    Thanks,
    Manjunath

Comments

  • ManjunathKN
    edited November 2022

    Hi @opolishchuk,


    Let me know if below solution works.

    ManjunathKN_0-1668545994454.png

    ManjunathKN_1-1668546068918.png

     

    above solution works only for present year and last month of quarter from selected quarter. if you want for all periods then remove the yearvalue in that formula and in movingsum keep upto 100/1000 basis on your timescale to get true for all future last months.

    Thanks,
    Manjunath

     

     

     

  • Olek P
    edited November 2022

    I'm a bit lost,

     

    what would the formula be for all periods?    

  • Olek P
    edited November 2022

    YOU ARE THE MAN  MANJUNATH!!!!

  • Olek P
    edited November 2022

    One more question.....

     

    How can I get this 10 million in ss4 to show up on all the booleans that are checked?

     

    I need that 10 million that comes from a single input "r21 fpa curve for travis " to go to all the checked booleans 

  • ManjunathKN
    edited November 2022

    @opolishchuk 

    I think i answered in other post to use yearvalue, but for all periods, yearvalue wont work, you need to use all periods of time.
    Override module

    ManjunathKN_0-1668550426994.png

    override value summary - Max

    BP View

    ManjunathKN_1-1668550495619.png

    Grid View

    ManjunathKN_2-1668550576004.png

     

    Thanks,
    Manjunath




  • ManjunathKN
    edited November 2022

    @opolishchuk 

     

    correction: in source module we can have many override values for different quarters in that case max summary won't work.

     

    Create one more line item in source and add logic if quarter select then override value else 0 and keep the summary as max. Use this line item for target module formula referencing.

     

    Thanks,

    Manjunath 

  • Olek P
    edited November 2022

    Hi Manjunath

     

    Your solutions work perfectley when the boolean is checked, however when the boolean is unchecked then the 10M appears in every third month for all time period. When it is unchecked I would like it to be 0 thanks

     

    (in the screenshots I selected March 23 and then unselected it

  • ManjunathKN
    edited November 2022

    Hi @opolishchuk ,

     

    Haah, i didnt do that check, apologies.

    could you add this logic:

    ManjunathKN_0-1668617780362.png

    ManjunathKN_1-1668617813068.png

     

    Thanks,
    Manjunath

     

  • Olek P
    edited November 2022

     

    No worries king. You're still the best

     

     

    It works perfectly now

  • ShubhamCh
    edited November 2022

    Hi @opolishchuk 

     

    I know @ManjunathKN has already solved the problem but I still tried to solve it because I found it interesting. 

    Here is how I solved it 

    •  Quarter Module - Where the boolean will be selected, dimensioned by Quarters
      • Two line items,
        • Check (Boolean)
        • Selected Quarter (Quarter) = IF Check THEN ITEM(Time) ELSE BLANK  || Summary - Firstnonblank

    Screenshot 2022-11-18 at 12.02.34 AM.png

    • Month Module, dimensioned by Month
      • Three line items
        • Parent (Quarter) = PARENT(ITEM(Time))
        • Serial number (Number) = PREVIOUS(Serial number) + 1
        • Final Boolean = IF Parent >= Quarter Module.Selected Quarter[SELECT: TIME.All Periods] THEN MOD(Serial number, 3) = 0 ELSE FALSE

    Screenshot 2022-11-18 at 12.10.52 AM.png

    See below how it is working side by side, 

    Screenshot 2022-11-18 at 12.12.05 AM.png

     

    My method requires a top level on time.  

    Thanks!

    - Shubham 

  • ShubhamCh
    edited November 2022

    Update:- The top level can be removed by using "Quarter to months" formula from @ManjunathKN solution.

  • ManjunathKN
    edited November 2022

    Hi @ShubhamCh,

     

    yes, using mod is much simplified way, but you cannot select first month of every quarters and it has some flaws when I previously worked with that, But you can even get first month of every quarter with item logic. Ex: Parent(item(time))<>previous(parent(item(time))).

     

    But I agree using mod(number,3) is much simpler than Parent(item(time))<>next(parent(item(time))) for above requirement.

    Thanks,
    Manjunath

  • Olek P
    edited November 2022

    Hi guys, 

     

    I have a bit of change that I can't figure out. I need to change it to one master override button. When this button is pressed I need all the inputs (ss11, comes from ss13) to be put into the master override amount(ss12). I'm having some trouble with the master override amount line item in ss14. I can't put SELECT: ALL Time Periods because it will put the amount at the earliest time period going forward. I need it to go into the 3rd month of the quarter where it was inputted and going forward. 

     

    Please let me know if you need clarification

  • ShubhamCh
    edited November 2022

    Hi @opolishchuk 

     

    In your "R21 ..." module add one line item :- Rolling Adjustments = IF Override THEN Adjustment ELSE PREVIOUS(Rolling Adjustments)

    In "C76 .." Master Override Amount write formula:- IF Final Boolean (Master Override Following Month) THEN Quarter Module.Rolling Adjustments[LOOKUP: Parent] ELSE 0

    Understand the naming convention from my solution below:- 

     

    Screenshot 2022-11-18 at 10.49.09 PM.png

    Observe the values when I override in different quarters.

    Screenshot 2022-11-18 at 10.50.25 PM.png

    Thanks!

    -Shubham Chaudhary