Need help in fetching last quarter /last half year

Highlighted
Contributor

Need help in fetching last quarter /last half year

Hi

I am trying to use a formula to fetch the last quarter and last half year. I have a line item 'Current Quarter' in my module which has the current quarter. 

ex.Q3 FY20. I want to apply formula on this line item to get the last quarter and last half year values.

Any help is appreciated

.

Thanks

Regarrds

Prabakaran

8 REPLIES 8
Highlighted
Master Anaplanner/Community Boss

Re: Need help in fetching last quarter /last half year

Hi Prabakaran,

Create 2 line items called Transaction Qty Quarter and Transaction Qty HY;
Change the Time Scale of both line items to Quarter and Half Year respectively

anirudh_0-1601266669951.png

The formula for both line items is simply "Transaction Quantity"

 

After this setup, the formula for the previous quarter data is:

Transaction Qty Quarter[LOOKUP: Previous Quarter]

and for previous half year:

Transaction Qty HY[LOOKUP: Previous Half Year]

 

The Previous Quarter and Previous Half Year must be formatted as Quarter and Half Year respectively

Let me know if you need more help

 

Regards,

Anirudh

Highlighted
Contributor

Re: Need help in fetching last quarter /last half year

Hi @anirudh 

Thanks for the response. When I follow your steps, i am getting error message when entering the formula for 

Transaction Qty Quarter. Please see the screen shot attached.

 

Thanks

Regards

Prabakaran

Highlighted
Master Anaplanner/Community Boss

Re: Need help in fetching last quarter /last half year

Hi Prabakaran,

The Transaction Qty Quarter and HY line items should be formatted to number

Regards,
Anirudh
Highlighted
Super Contributor

Re: Need help in fetching last quarter /last half year

I would not create line items with different dimensions to the parent module. Instead create a dedicated module to hold the data at the required time dimension and refer to that module when pulling the data into you target module.

You may wish to reference this data in other modules. If you house it in the separate module you will be able to reference it more efficiently over multiple target modules.
Highlighted
Master Anaplanner/Community Boss

Re: Need help in fetching last quarter /last half year

@praba71 

 

First, not sure why you have so many Text line items, Text is not good for performance reasons.  Second, as @ChrisHeathcote stated, you don't want to do your logic in the transactional module,but in a SYS Module like the below which is dimensionalized by Quarters.

 

Create 3 line items:

Year: item(Time)       - Formatted to Time Period Year

Last Quarter: PERIOD(END(Year)).  - Formatted to Quarter

Half Year: PARENT(Last Quarter).   - Formatted to Half Year

 

2020-09-28_07-21-32.png

 

2020-09-28_07-22-06.png

 

Rob

Highlighted
Contributor

Re: Need help in fetching last quarter /last half year

Thanks @rob_marshall .. Will try our your suggestion.

Once correction.. What I meant by Last Quarter is the Quarter that preceded the current one. eg. Current Quarter is Q3FY20 and I want to fetch Q2FY20 through a formula.. Sorry for the confusion. Is there any formula to capture the previous time periods? 

 

Regards

Prabakaran

Highlighted
Master Anaplanner/Community Boss

Re: Need help in fetching last quarter /last half year

@praba71 

 

Yes, in the module above (SYS Week), create a line item named Quarter_Item formatted as Quarter.  In the last Quarter line item, the formula will be Quarter_Item - 1.

 

Rob

Highlighted
Contributor

Re: Need help in fetching last quarter /last half year

thanks @rob_marshall @ChrisHeathcote for the inputs..

 

Regards

Prabakaran