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

Olek P

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

ManjunathKN

@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

ManjunathKN

Hi @opolishchuk,

Let me know if below solution works.

 

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

I'm a bit lost,

 

what would the formula be for all periods?    

Olek P

YOU ARE THE MAN  MANJUNATH!!!!

Olek P

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

@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

override value summary - Max

BP View

Grid View

 

Thanks,
Manjunath

ManjunathKN

@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

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

Hi @opolishchuk ,

 

Haah, i didnt do that check, apologies.

could you add this logic:

 

Thanks,
Manjunath

 

Olek P

 

No worries king. You're still the best

 

 

It works perfectly now

ShubhamCh

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

• 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

See below how it is working side by side, 

 

 

My method requires a top level on time.  

Thanks!

- Shubham 

ShubhamCh

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

ManjunathKN

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

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

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

 

Observe the values when I override in different quarters.

Thanks!

-Shubham Chaudhary