Custom Filter

UMAMAHESH
UMAMAHESH
in Modeling

Hi Experts,

 

I have a requirement.i am confusing how to resolve this kindly help on this.

 

In One module       Row Axis - Product and Customer lists
                              Column axis - Lime Item and time lists
                               Page Axis - Measure list

There are 5 line items. For each line item i want to saw different time periods that also based on user selection.

 

For suppose user selects 2018 year
Then
Line item 1 to 3: Show all years
Line Item 4 : Show 2018 year only (Its possible by using filter but when i applying filter it shows only 2018 year and line item 4 only)


But as per my requirement i want show line item 1 to 3 all years and line item 4 specific selection period.

Capture.PNG

I Line item 4 period based on user selection.

 

 

Regards,

Uma

 

Is it possible. Kindly suggest me on this

Tagged:

Best Answer

  • DavidSmith
    DavidSmith
    Answer ✓

    @UMAMAHESH 

    As you have found, filtering on nested dimensions is tricky.

    However, if you look at your problem a different way, you are wanting to display a different single time value depending on the line item. So you don't actually need a timescale in the report

    Lets assume I have the following (I've used random numbers!!):

    2019-05-23_09-37-00.png

     

    1. Create a Line Item subset using the module above

    2019-05-23_09-38-46.png

    2. Create a staging module based on the module from above, but include the Line Item Subset from 1. and a single line item with COLLECT() as the formula

    2019-05-23_09-40-30.png

    Remember to turn the summaries off for the lists (you have to leave Time summaries on) 

    3. Create a second module using the Line Item Subset with a single Boolean line item. This is where you define the line items that will reference the user choice from 4. below

    2019-05-23_09-44-04.png

    4. Create a module to define the Time period for the dynamic selection.  A single line item formatted as Year

    2019-05-23_10-16-47.png

     5. Create a reporting module based on 1. but without the timescale

    2019-05-23_10-19-50.png

     

     The formula is IF NOT Line Items Selection.Dynamic? THEN KPI Staging.Value[SELECT: TIME.All Periods] ELSE KPI Staging.Value[LOOKUP: Filter Selection.Year]

    We put the most common occurrence first as a Best Practice

    6.

    2019-05-23_10-00-48.png

     

    2019-05-23_10-02-25.png

     

    A couple of extra tips:

    1. As @usman.zia  mentioned, if you need the report and filter setting to vary by user, then you will need to ensure that the modules created in 4 and 5 have the user list within them

    2. If you needed the second selection to be dynamic, you could set up a second line item in the module from 4 to hold the second variable and adjust the formula in 5 accordingly.

    I hope that helps

    David

Answers

  • usman.zia
    usman.zia

    Hi Uma, 

     

    Is the 4th line item dimensioned by user?

    Is the filter that applies to the 4th line item dimensioned by user? 

    Does the formula of the 4th line item reference the user filter? 

    I would suggest creating these and multiplying the line item by the flag or by using an if statement. 

     

    I hope that this helps @UMAMAHESH 

     

    Thanks,

     

    Usman

  • UMAMAHESH
    UMAMAHESH
    Hi Usman, Could you please explain in brief. Regards, Mahesh

Categories