How to Apply Filter in a module with respect to Dynamic Time filter

ankitaagarwal
ankitaagarwal
in Modeling

Hi,

So I ran into a situation where we had provided User specific Dynamic Time filter for Months/Quarters/Year. Now I want to Display only Non-zero Values in my Module W.r.t the Dynamic Time Filter. So, If I select Quarter FY25 then only those line items should appear which has values and when I change the Dynamic Time Filter value to FY24 then those line items should display where values are Non-Zero for FY24. Right now I have applied Filter taking FY25 time in the module which will not work for FY24 and Other time scale. Any suggestions to solve this problem. Below is the Screenshot of the Dynamic Filter

image.png

Answers

  • Dikshant
    Dikshant

    @ankitaagarwal,

    Can you please share the screenshot of the final module where you want this filter to be applicable?

  • ankitaagarwal
    ankitaagarwal
    image.png

    This is the final Module

  • rob_marshall
    rob_marshall

    @ankitaagarwal

    I got you covered, but I am not sure the juice is worth the squeeze.

    If the above functionality will be used by multiple users, you will need to dimensionalize the results and selections by Users. Let me show you.

    I created a list for the Month, Quarter, Year and dimensionalized this by Time (at the Year granularity).

    It has to be dimensionalized by Users to ensure everyone's selections are correct.

    image.png
    image.png

    Create SYS Dynamic Time, dimensionalized by Users and Time.

    image.png

    Go ahead and create the line items, but don't fill in the formulas because you will need to create a Line Ite Subset. Also, be mindful of the Format, Summary, as well as the time scale for the above line items.

    Create a Line Item Subset, LISS Month Qtr Year which uses the SYS Dynamic Time module (the one above).

    image.png

    and select these line items

    image.png

    Create SYS LISS Mon Qtr Year module with the line item subset you just created in the Applies To with a single line item named Map. This should be list formatted to the list you had in the very first picture (the selection).

    image.png
    image.png

    Now go back to the SYS Dynamic Time module and we can start filling in the logic/formulas:

    image.png

    Month Nbr, Quarter Nbr, and Year Nbr will all have the same formula, the only difference in them is the Timescale granularity (Month, Quarter, Year):
    IF Dynamic Time Ankit.Display?[LOOKUP: Year, LOOKUP: SYS LISS Mon Qtr Year.Map] THEN 1 ELSE 0

    Month?= Month Nbr = 1
    Quarter?: Quarter Nbr=1
    Year?: Year Nbr = 1

    Again, on the above, make sure your Time scale is set correctly for each one

    image.png

    Quarter: Parent(Time) with the summary being Last Non Blank

    Year: Parent(Quarter), again with the summary being Last Non Blank

    Filter: Month? or Quarter? or Year with the Summary being Formula

    The final Reporting module….Create a module dimensionalized by Users with a Time Scale of Month.

    image.png

    Data: is getting the data , your PL04 Global

    Filter: Boolean formatted with the formula Data <> 0 AND SYS Dynamic Time.Filter.

    Create a filter where you are filtering on Filter?

    image.png

    Now, you can test it.

    image.png

    Results in:

    image.png
    image.png

    Results in

    image.png
    image.png

    Results in:

    image.png

    Hope this helps,

    Rob

  • ankitaagarwal
    ankitaagarwal

    Hi Thanks for your response but I have Some 15-20 modules where I want to Apply this filter which are not user specific than how can this be helpful, If we have any other way to Apply in all our modules that would be of great help!

  • rob_marshall
    rob_marshall

    @ankitaagarwal

    The reason you have to use the User dimension is due to the fact the filter is transactional-based (based on line item values) vs dimensional-based. As I said in my early post, I am not sure the juice is worth the squeeze.

Categories