1 Reply Latest reply on Dec 3, 2018 10:40 AM by Michel Caissie

    Filtering to include overlapping rows

    David Ghan

      Greetings!

       

      I have some data that give amounts by category and by year. So for example for browser use, one column is "browser_mobile_3mo" and another is "browser_mobile_12mo". I'm trying to create some filters for a dashboard that is dedicated to returning nothing more than the number of rows. So for example I might want to look at the past 3 months of browser uses, regardless of the type of browser. So I would simple select  "3 months" on a drop down filter. Or lets say I want to select the type of browser as "desktop" and look at the number of overall visits via a desktop browser.

       

      I'm having trouble determining the best course of action to ensure my filters are not exclusive and leaving out other relevant rows. I tried to create a CASE statement that might work to achieve this, but I'm not sure if I first need to restructure the data prior to creating these filters or parameters. I've attached the workbook and sample data to give you an idea of what I'm working with. Hopefully that makes sense. If not I can explain further.

       

      Thanks!

        • 1. Re: Filtering to include overlapping rows
          Michel Caissie

          David,

           

          Since your data can have for example,  a 1 on both  Browser App 12 Mo  and  Browser App 3Mo,  then the calculation

          if

          [Browser App 3Mo] = 1 or

          [Browser Desktop 3Mo] = 1 or

          [Browser Mobile 3Mo] = 1

          THEN '3_MONTHS'

          elseif

          [Browser App 12Mo] = 1 or

          [Browser Desktop 12Mo] = 1 or

          [Browser Mobile 12Mo] = 1

          THEN '12_MONTHS'

          end

          will return  '3_MONTHS'  on rows that should also be  '12_MONTHS'

          In other words,  you are trying to create a dimension and give two values per row for that dimension, which is obviously not possible.

           

          What you can do is to Pivot all your measures (Go in Data Source - Ctrl select all measures columns, right-click and Pivot).

          You will now have a dimension Pivot Field Names  and a Measure  Pivot Field Values.

           

          From this you can compute  BROWSER_USE with

          if

          [Pivot Field Names] = 'Browser App 3Mo' or

          [Pivot Field Names] = 'Browser App 12Mo'

          then 'APP'

          elseif

          [Pivot Field Names] = 'Browser Desktop 3Mo' or

          [Pivot Field Names] = 'Browser Desktop 12Mo'

          then 'DESKTOP'

          elseif

          [Pivot Field Names] = 'Browser Mobile 3Mo' or

          [Pivot Field Names] = 'Browser Mobile 12Mo'

          then 'MOBILE'

          end

           

          and BROWSE_timeframe with

          if

          [Pivot Field Names] = 'Browser App 3Mo' or

          [Pivot Field Names] = 'Browser Desktop 3Mo' or

          [Pivot Field Names] = 'Browser Mobile 3Mo'

          then '3_MONTHS'

          elseif

          [Pivot Field Names] = 'Browser App 12Mo' or

          [Pivot Field Names] = 'Browser Desktop 12Mo' or

          [Pivot Field Names] = 'Browser Mobile 12Mo'

          then '12_MONTHS'

          end

           

          With only those two filters you can have the numbers for any combination of USE and timeframe.

          Note you also have to put the Pivot Field Values in the filters  and keep only the 1s.

           

          PS:  when posting, instead of attaching a zip file, you can  extract the data  and  save as .twbx.  This will give a single file including the views and the data.

           

          Michel