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

Tagged:

Best Answer

  • @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

Answers

  • 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

  • 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

  • Hi Prabakaran,

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

    Regards,
    Anirudh
  • 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.
  • @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

     

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

     

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

     

    Rob

  • 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

  • thanks @rob_marshall @ChrisAHeathcote for the inputs..

     

    Regards

    Prabakaran