Highlighted
Contributor

Formula to number months within a quarter?

Within Q1, January is the first month (1), February the second (2), March the (3rd). Then Q2 starts, and April is the first (1), May is the second (2), and so on. 

 

Is there a good way to set something like this up?

 

I ask because I want to build an IF AND statement, in which IF a value exists within a cell AND that cell is #2 within that quarter, a cell will be populated.

5 REPLIES 5
Community Boss

Re: Formula to number months within a quarter?

@RobinSilk 

Use QUARTERTODATE(1) function

This increments and resets, at the end of each quarter

2019-11-12_16-49-17.png

 I hope that helps

David

Frequent Contributor

Re: Formula to number months within a quarter?

Hi,

 

I think the function QUARTERTODATE will help here;

 

https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/QUARTERTODATE.html?_ga=2.2226929... 

 

In your time settings module, have a period count line item, value of "1", and then have your QUARTERTODATE line item reference this line item.

 

Best,

 

Callum

 

Contributor

Re: Formula to number months within a quarter?

I am not sure using QUARTERTODATE as described by others gives you the full explanation.

 

If you are looking to have a query which directs the outcome to a specific action such as the 2nd period of each quarter results in a certain outcome etc. then you need to create a custom mapping against time.quarters which will direct the query to the desired action. 

 

If this is so, create a numbered list corresponding to each desired unique action.

 

Then map this list to each quarter in a time settings systems module. 

 

In your target create your IF THEN statement using this systems module to map to the desired action. 

 

Example - IF targetcell.value[LOOKUP: mapping.unique action] then x else next query...etc

 

*  by Action I refer to any desired outcome which has been mapped in the time setting systems module. 

 

Hope this helps. 

 

Chris

Contributor

Re: Formula to number months within a quarter?

Using QUARTERTODATE is simply an automated way of populating the mapping and would maintain the mapping as time frames are updated. Otherwise the mapping would need to be manually updated each time the model calendar was changed.

Perhaps my contribution is more of an explanation as to why this would be a good approach!!
Community Boss

Re: Formula to number months within a quarter?

If you do need to check for the 1st, 2nd or 3rd periods in the qtr, I would also do this in the time settings module as the calculation doesn't change in relation to other dimensions

2019-11-12_17-15-50.png

 David