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

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

Accepted answers

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

All comments

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?

ManjunathKN

@opolishchuk

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

ss4_152119.png

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

ss5_152206.png

ss6_152206.png

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

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

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

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

Quick Links

Can you help?

Unanswered questions

Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…