Need help in fetching last quarter /last half year

praba71

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

rob_marshall

@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

anirudh

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

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

praba71

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

anirudh

Hi Prabakaran,

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

Regards,
Anirudh

ChrisAHeathcote

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.

rob_marshall

@praba71 

 

First, not sure why you have so many Text line items, Text is not good for performance reasons.  Second, as @ChrisAHeathcote 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

 

 

 

Rob

praba71

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

praba71

thanks @rob_marshall @ChrisAHeathcote for the inputs..

 

Regards

Prabakaran