3 Replies Latest reply on Aug 15, 2016 10:27 AM by Kes Harding

    Window calc / LOD help (Can't think of a snappy summary!)

    Kes Harding

      Hi Everyone,

       

      I'm trying to create a viz of the way that a selection of new lines have been distributed to stores. On one axis I want the number units allocated in the first hit. The other axis I want the count of stores and product lines (called options) that received that many units. So 200 product/store combinations got 1 units, 150 got 2 units etc. Seems very straight forward but I can't get it to work.

       

      The problem I'm having is the fact that my data is at a smaller grain than Option, because each option contains multiple sizes (SKUs). I've tried various things using both table calculations and level of detail calculations but I can't get either to do what I want. In order to show what I'm trying to achieve I've cheated by creating a cross tab at the option level, exporting to Excel and then re-importing as a new connection and using the quantity as a dimension. This is not much use because I'll be doing a lot of different pieces around this and ultimately I want to create refreshable dashboard around a much larger set of data (the extract in the attached is tiny snapshot.)

       

      So what I'm hoping one of you Tableau geniuses can do is recreate that viz directly from the first, lower grain data source.

       

      Many thanks in advance,

       

      Kes

        • 1. Re: Window calc / LOD help (Can't think of a snappy summary!)
          Carl Slifer

          Howdy Kes,

           

          There are three steps to solve this problem.

          First Create a combined field. I only do this  because I'm a tad lazy to write formulas when I don't have to and because an LOD function (in step 2) can only use fields that already exist or have been created previiously. Ctrl+Select the branch code and option code fields then choose create -> Create combined field

           

          Second Create an LOD that (based on the above groupings returns the number of initial allocations). Place this onto the columns shelf, right click it and change to 'dimension'

          {FIXED [Branch Code & Option Code (Combined)]: SUM([Initial Allocation Qty])}

           

          Step Three is the create a calculated field that returns the number of distinct branch code and options. Place this on your rows shelf.

          COUNTD([Branch Code]+[Option Code])

           

          And then it's all done.

           

          Cheers!

          Carl Slifer

          InterWorks

          1 of 1 people found this helpful
          • 2. Re: Window calc / LOD help (Can't think of a snappy summary!)
            Michael Hesser

            Wonderful work & explanation, Carl!

            • 3. Re: Window calc / LOD help (Can't think of a snappy summary!)
              Kes Harding

              Hi Carl,

               

              Many thanks for the response.

               

              What you outline is almost exactly what I originally envisgaed. The exception being that I didn't try to combine the fields. You can see in my workbook I've created my LOD calculation as follows:

               

              {include [Option Code],[Branch Code] : sum([Initial Allocation Qty]) }

               

              This seems to work exactly as expected. For example if I average this measure I get the same value as if I average the measure outputted via my cheat sheet. (Incidentally, when I tried using a combined field in place of [Option Code],[Branch Code] I get the error "Combined fields and sets are not permitted in the dimensionality declaration.")

               

              The big problem I have is when try to use my LOD calculation as a dimension, as you suggested in step 2. The option simply isn't there (see screenshot). With my cheat sheet version the option to do this (with a regular measure) is there and this is how I got my "What I want" sheet to work.

               

              No dimension in menu.png

               

              Would you mind having a go at implementing your solution to the workbook I attached and uploading yur updated version to the thread please?

               

              Many thanks in advance,

               

              Kes