5 Replies Latest reply on Dec 4, 2018 11:27 AM by Joe Oppelt

    How do I compare data to a benchmark that it is not part of?

    Bryan Hallock

      For example, a store located in the West region. I know how to use Fixed LOD calcs and context filters to compare against the West region or nationally. But how would I compare that store to the East region (i.e. one that it is not part of)? Thanks.

        • 1. Re: How do I compare data to a benchmark that it is not part of?
          Joe Oppelt

          If you have a calc like this:

           

          { FIXED [Region] : SUM([ Whatever]) ) }

           

          Then you could make a separate calc just for East:

           

          { FIXED : MAX( if [Region] = "East" then [That previous calc] END) }

           

          I did MAX because I don't want to SUM up multiples of the value for all the EAST rows.  So I get just one value, and this calc,, since there is no dimension listed, is available for all rows in the data source.

          • 2. Re: How do I compare data to a benchmark that it is not part of?
            Bryan Hallock

            I don't want to hardcode a value. Will this work if I have a parameter set to control "Region"? I think so. Will this work if I have multiple parameters controlling multiple filters that the individual data point may or may not be a part of? E.g. Region, Store Manager, Store Size? Thanks.

            • 3. Re: How do I compare data to a benchmark that it is not part of?
              Joe Oppelt

              Absolutely, yes.

               

              And I'm with you.  I hate hard-coding values.

               

              In fact, my original suggestion would require you to have x-many separate calcs for each region.  I didn't know how you were planning to use this calc.

               

              So if you have a parameter, then you need only one calc, and it will contain the value the user selects from the parameter.

              • 4. Re: How do I compare data to a benchmark that it is not part of?
                Bryan Hallock

                Thanks Joe. This worked for me. I added the parameter logic. Then added a second parameter to test multiple filters on the calculation. The "store" the report is run for will now allow the user to select a benchmark, regardless of whether that store is contained within that benchmark.

                 

                Not totally sure "why" it works though. Can you elaborate on what you said above? "this calc, since there is no dimension listed, is available for all rows in the data source"

                • 5. Re: How do I compare data to a benchmark that it is not part of?
                  Joe Oppelt

                  Bryan Hallock wrote:

                   

                  ...

                   

                  Not totally sure "why" it works though. Can you elaborate on what you said above? "this calc, since there is no dimension listed, is available for all rows in the data source"

                  When you do a LOD and specify a dimension before the colon, it tells tableau to evaluate the value for each occurrence of that dimension.  (And if there are multiple dimensions listed, Tableau evaluates for each unique combination of all those dimensions.)  So the first calc has [region] in the dimension list.  For all the rows of a given region, the resulting value is assigned.  All the "East" rows get the "East" value, and likewise "West", etc.

                   

                  When there is no dimension listed at all, then you will arrive at one overall value assigned for that calc on every row.

                   

                  The calc I suggested said to look only at all the rows for "East" (and you expanded the principle to look at all the rows for the selected parameter value.)  Therefore, for all rows in the database (since no dimension is specified), evaluate the logic inside the parentheses, and load that value in all rows.  The logic we have in the parens says to consider only those rows in a given region, and grab the value from our first LOD that was assigned for that region.  (And all the rows for that region have the same value within that region.)

                  1 of 1 people found this helpful