SELECT/LOOKUP formula

Dominika_Dudzinska
Dominika_Dudzinska
in Modeling

Hello, 

I need to write a formula to taka data from one module, but only for current month. 

I can not use LOOKUP function, because dimension doesn't match. 

 

e.g. When the formula includes selecting a specific month by SELECT: Month.October everything works, but I want to give the user the option to select the current month.

 

Can you advise something?

Thank you!

 

 

Tagged:

Answers

  • Thijso
    Thijso
    Hi Dominika,
    I think you should create a line item "Current Period" where the formula is PERIOD(CURRENTPERIODSTART()) and the formatting is set to "Period - Month" => This will give you the current month

    Create a second line item with the select function where you use your first line item as an argument : X [SELECT: TIME.'Current Period']

    Hope this helps.

    Grts,
  • kevin.cho
    kevin.cho

    If you're using a bare SELECT on a single time period, then a LOOKUP should work fine as well - as long as the line item you're using to do the LOOKUP on is dimensioned appropriately. 

     

    If the time period you want to retrieve is a constant (across all other dimensions), then create a line item formatted as "Time Period" (Month) with no dimensionality applied (clear the "Applies To", turn off Time and Versions). 

     

    You can then write your formula as = Source Line Item[LOOKUP: Time Period]

     

    where "Source Line Item" is your source line item dimensioned against Time, and Time Period is the newly created line item with no dimensions applied

  • rob_marshall
    rob_marshall

    @Dominika_Dudzinska 

     

    @kevin.cho is absolutely correct.  In addition, you should only use SELECT statements on structured data (top members of lists, Native Versions, and keywords for Time (All Time Periods)

     

    Rob

Categories