6 Replies Latest reply on May 4, 2018 1:28 AM by Yuriy Fal

    Apply 2 Different Selections of 1 Filter to the Same Measure

    Steffi B

      Situation: 3 Dashboard filters, namely Date (relative date filter), Error Code, and Aggregator applied to multiple worksheets. This worksheet has a graph that displays number of users over days.

      Objective: To have 2 lines overlayed on this graph (i.e. dual-axis.) The first one (showed below) representing number of users over date selected, and another representing number of users over date period before that.

      Example: If relative date chosen is "Last 6 days" - the 1 line should show the most recent 6 days of "UserCount", while the 2nd line should show the 6-day period before that (i.e. 12-6 days ago) for "UserCount"

      What I have currently:

      1. Calculated field called "DateSelected": DATEDIFF('day', MIN([Date]),MAX([Date])) + 2))

                     -i.e. if "Last 6 days" is selected then DateSelected = 6 (added +2 as for some reason it was always 2 days under)

          2. Calculated field called "CurrPeriodUsers": IF [Date] < (TODAY() - [DateSelected] + 1) OR [Date]>(TODAY() + 1) then false else true end

                    - ie Show date if it's between 6 days ago and today

          3. Calculated field called "LastPeriodUsers": IF [Date] < (TODAY() - [DateSelected]*2 + 1) OR [Date]>(TODAY() - [DateSelected]+ 1) then false else true end

          -i.e. Show date if it's between 12- 6 days ago (side note: I get error "cannot mix aggregate and non-aggregate functions even though "DateSelected" is an integer)

       

      - but I don't know how to apply "CurrPeriodUsers" and "LastPeriodUsers" simultaneously to "UsersCount" to get 2 different lines. I think I have to use LOD expressions but I don't properly understand them yet (tried with turning "DateSelected" into a proper date (instead of an integer) and having {fixed [Error Code], [Aggregator], [Date]: sum([UsersCount])} and then another LOD swapping out [Date] for [DateSelected].)

      Screenshot (166).png