3 Replies Latest reply on Dec 7, 2018 3:46 PM by Vinnie Ahuja

    [10.1] How to filter data with an inclusive "OR" instead of an "AND"?

    Tyler Shea

      EDIT: I tried to write my question and problem below, in response to Vinnie

       

      Below is a table of my data (this is merely an example of the actual data, as I at this moment want want to post it online).

       

      Stop NameStop IDBus IDFares Collected
      Main St1110
      2nd St215
      Community College3130
      University4150
      HWY 35250
      Community College32100
      Costco6220
      Starbucks7320
      I 40583100
      2nd St235
      3rd St940
      4th St1045

       

       

      What I'd like to do if possible, is to be able to get a total fare amount for any combination of the three Dimensions. For instance, my customer may want to the total fares for all of Bus 1, the fares for Stop ID numbers 2 and 3, as well as for 4th Street.

       

      I have been able to do this (on a workbook not included) if I only filter the fares based off a single dimension.

       

      On the workbook included:

       

      Sheet 1 - Bus ID, Sop ID, and Stop Name (multi-value) filters, but when I select a value combination that doesn't exist (like BUS ID 1, Stop ID 2, Stop Name Community College) I get a Total Fare amount of 0

      Sheet 2 - I have some Parameter filters, but I can only select one value of each parameter, where ideally, I'd be able to select multiple extra stops, or buses, etc.

       

      I hope this makes some sense, as I am struggling with putting this into words..

        • 1. Re: [10.1] How to filter data with an inclusive "OR" instead of an "AND"?
          Vinnie Ahuja

          Hi Tyler,

           

          If I am understanding correctly, a LOD calc should do this for you:

           

           

          { FIXED [Bus ID], [Stop ID], [Stop Name]: sum([Fares Collected])}

           

          Cheers

          Vinnie

          • 2. Re: [10.1] How to filter data with an inclusive "OR" instead of an "AND"?
            Tyler Shea

            Hi Vinnie,

             

            Unless I'm misinterpreting your solution, it still won't get me the total fare amount for all of Bus 1, Stops 2/3, and 4th Street.

             

            I'll try to clarify my above statements:

             

            I would like to display the total fare amount for all of Bus 1, Stops 2/3, and 4th Street, via filters/drop-down menus.

             

            So ultimately I can have any combination of buses, stop names/IDs be apart of that display.

             

            The problem being, when if I make a select of two different filters that have no overlap (Bus 1 and Stop 9) the total fare amount I get is zero.

            • 3. Re: [10.1] How to filter data with an inclusive "OR" instead of an "AND"?
              Vinnie Ahuja

              Hi Tyler -

              My head is still spinning a bit but I think I have a better understanding.  I think your solution lies in Level Of Detail Calculations, however rather than one calc as I previously suggested, perhaps you need one for each of these dimensions that only considers that dimension.

               

              {FIXED [Stop ID]: sum([Fares Collected])}

              {FIXED [Bus ID]: sum([Fares Collected])}

              {FIXED [Stop Name]: sum([Fares Collected])}

               

              The idea is you only want your Bus ID total subject to the Bus ID filter and none of the rest, Stop ID only subject to the Stop ID filter etc.  So if I filter the below for Stop ID or Stop Name, the Bus ID Fare Collected will always be 95 for Bus 1.

               

              You could then add these together in another calc to get a total.

               

              I don't think this is your complete answer, how you design the viz will play a role, but it should get you closer.

               

              Cheers

              VInnie