1 Reply Latest reply on Mar 21, 2013 8:22 AM by Jim Wahl

    Higher Level Calculation

    Raina S

      Hi,

       

      Can anyone help me with higher level calculation. Attached is the sample workbook.

      Scenario is explained below..

       

       

      1. I have 5 SEGMENTS--> Each Segment has many STORES----> Each Store has many ASSETS

      2. Views are filtered based on the STORE.

      3. In my first view, I have 1st Column -Assets, 2nd and 3rd Column - work order types 'CM' and 'PM' for which numbers are Work Order Counts. I created a calculative field STORE RATIO%- sum(CM)/sum(PM)- 4th Column.

       

      4. 5 th Column- Segment Ratio, which should contain sum(cm)/sum(pm) for all the stores in the segment the store selected is in.

       

      In the attached workbook, I have 2 sheets

      1st Sheet - Store 2528 is selected and it belongs to segment 5 - For Asset Baby_Changing_Table the store ratio is 0

      2nd Sheet - contains all stores for segment 5 - For Asset Baby_Changing_Table the Segment ratio is 21%

       

      I want these 2 ratios to appear in the single tab.

       

      AIM : Require Store Ratio and Segement Ratio in single sheet.

       

      Pls help!!

       

      Rajina

        • 1. Re: Higher Level Calculation
          Jim Wahl

          Hi Raina,

           

          You can use a table calc filter to apply a "visual" filter without changing the underlying data used to calculate aggregates. This works because table calc filters are applied after table calculations.

           

          Step 1: Create Segment %

          Create a new calculated field, Segment %

          WINDOW_SUM(COUNT([CM]))/WINDOW_SUM(COUNT([PM]))

           

          This looks similar to your Store %, but uses the table calc window function. To add this to your view, first add Segment and Store to the Rows shelf. You'll need these to adjust the partition / window the table calc operates in.

           

          Next add the Segment % to the measure values shelf, right-click on the pill > Edit Table Calculation > Compute Using > Advanced and add Asset, Segment and Store to the right-hand "Compute using" box (in that order). Click OK. Select restarting every Segment. Now the partition window for your formula is all the store values for each Asset and Segment.

           

          Step 2: Add STORE Table Cal for filtering

          Create another calculated field called STORE Table Calc with the formula:

          lookup(min([STORE]),0)

           

          The formula here is not that important so long as you get all of the STORE values, which we'll use as a filter.

           

          Add this to the Filter shelf. Now you can filter by the table calc, but the Segment % table calculation has already been computed and is not affected by the table calc filter.

           

          See attached. There's another example of this technique here: A Jedi (Filter and Table Calc) Trick | Tableau Software

           

          Jim