Filtering Row List items dependent on Current Page - Line Item Value

MarkTurkenburg_*8288
MarkTurkenburg_*8288
in Modeling

Hi Team,

 

I have a module with Employee List on the the Rows, Time as Columns and Line Items as Pages. I would like to Filter the Employee items on whether the Time Value selected is greater than zero for the Current Page Line Item

 

It seems you can use Current Page for all other dimension filters except for Line Items where you need to select a Line Item to use as your filter

 

Is there a workaround for this?

 

Thanks

 

Mark

Tagged:

Answers

  • Yash1
    Yash1

    Hi @MarkTurkenburg ,

     

    Can you share few screenshots please. I dont understand the problem completely.

     

    Cheers,

    Yash

  • MarkTurkenburg_*8288
    MarkTurkenburg_*8288

    Hi @Yash1 

    Below is module with the Line item as Pages. I wish for the Filter to be depedent on whether the Current Line Item for the Row has a value greater than zero for FY21

    IMG1.PNG

     

    You can only select a Line Item to use for the filter, not the Current Page like all other dimensions

    IMG2.PNG

  • MarkTurkenburg_*8288
    MarkTurkenburg_*8288

    Hi @Yash1 

     

    I have solved the problem by what I call 'Transforming' the data. By that I mean that I created a Line Item Subset from the Line Items in the original module, created a replica of the original module with the line items removed and the Line Item Subset added. I then use Collect() & when you Filter you can use 'Current Page' on the Line Item Subset dimension

     

  • jasonblinn
    jasonblinn

    @MarkTurkenburg 

    Sounds like you got it figured out with the LIS, nice work!

     

    One additional suggestion here would be that you currently have FY21 selected in your filter meaning when you roll your model forward a year, you would need to update your filter. 

    jasonblinn_0-1592369044132.png

     

     

    I might recommend here to have a Time Settings module that would have a line that was Called "Next Year" Or something of that nature. This cell would just house the period of "FY21" in it.

     

    Then you could create a filter (Bool) line that has the following formula:  Data Line Item[Lookup: Time Settings.Next Year] <> 0

    This new line would not have the Time dimension as we have accounted for that with our lookup.

     

    Just a suggestion to make your maintenance easier when the annual rollover time comes!

     

    Jason

  • MarkTurkenburg_*8288
    MarkTurkenburg_*8288

    Thanks @jasonblinn 

     

    Yes I know I have broken all kinds of best practices by using FY21 rather than a dynamic value but this is for the FY21 Plan & I know next year the model will look completely different and these modules won't be used...still not best practice!

     

    Mark

     

     

  • einas.ibrahim
    einas.ibrahim

    Hey @MarkTurkenburg 

    I'm calling the Best Practice Police 😀

    I just want to explain why you couldn't select "Current Page" in your situation.

    Current Page, as you may know, means the current List Item; that translates to "apply this filter on any selection of this Particular List (Budget Owner, Currency.....etc)."
    Since Line Items are not a List, Current Page doesn't apply. 
    That's why the LIS solution provided by @Yash1 works since the Line Items are technically a list now.

  • MarkTurkenburg_*8288
    MarkTurkenburg_*8288

    Haha - yes it's a bit shaming putting my SELECT:Time work up on the forums....oh the shame 😂

     

    Thanks

Categories