6 Replies Latest reply: May 17, 2017 7:17 AM by Bart Schockaert RSS

    How to get set analysis to work using multiple bi-dimension conditions in a pivot table

    Bart Schockaert

      The following question relates to set analysis, using a combination of conditions based on equating 2 dimensions.

       

      Please see the attached screen-captures & QV file.

       

      I can solve the problem with an elaborate formula for calculating an expression (see WorkingElaborateFormula.JPG and lower here), but as the possible values for dimensions (and number of dimensions) increase, this solution is not sustainable.

       

      To illustrate what I cannot get to work, I have created a simple example of the data (see Script.JPG) & pivottable (see OutputView.JPG) . There must be a 'simple' solution to this, but so far it eludes me...

       

      What I want to do is to change this (working) formula:

       

      =pick(Rep1_ItemCode,

      // 1 = Revenue

      pick(Rep1_BLCode,

        // 1 = AA

        pick(Rep1_EntCode,

         // 1 = BE

         sum({<Item_Rep1 *= {'I1'}, BL_Rep1 *= {'AA'}, Ent_Rep1 *= {'BE'} > } [Val_Rep1]),

         // 2 = NL

         sum({<Item_Rep1 *= {'I1'}, BL_Rep1 *= {'AA'}, Ent_Rep1 *= {'NL'} > } [Val_Rep1])

         ),

        // 2 = BB

        pick(Rep1_EntCode,

         // 1 = BE

         sum({<Item_Rep1 *= {'I1'}, BL_Rep1 *= {'BB'}, Ent_Rep1 *= {'BE'} > } [Val_Rep1]),

         // 2 = NL

         sum({<Item_Rep1 *= {'I1'}, BL_Rep1 *= {'BB'}, Ent_Rep1 *= {'NL'} > } [Val_Rep1])

         )

        ),

       

      // 2 = Cost

      pick(Rep1_BLCode,

        // 1 = AA

        pick(Rep1_EntCode,

         // 1 = BE

         sum({<Item_Rep1 *= {'I2'}, BL_Rep1 *= {'AA'}, Ent_Rep1 *= {'BE'} > } [Val_Rep1]),

         // 2 = NL

         sum({<Item_Rep1 *= {'I2'}, BL_Rep1 *= {'AA'}, Ent_Rep1 *= {'NL'} > } [Val_Rep1])

         ),

        // 2 = BB

        pick(Rep1_EntCode,

         // 1 = BE

         sum({<Item_Rep1 *= {'I2'}, BL_Rep1 *= {'BB'}, Ent_Rep1 *= {'BE'} > } [Val_Rep1]),

         // 2 = NL

         sum({<Item_Rep1 *= {'I2'}, BL_Rep1 *= {'BB'}, Ent_Rep1 *= {'NL'} > } [Val_Rep1])

         )

        )

      )

       

      to something as easy as (conceptually):

       

      =sum({<Item_Rep1 *= {Rep1_Item}, BL_Rep1 *= {Rep1_BL}, Ent_Rep1 *= {Rep1_Ent} > } [Val_Rep1])

       

      So the 3 data items match the relevant 3 report line items and show the correct amount in every cell in the pivot table

       

      My failed attampts can be found in FailedFormulas.JPG.

       

       

      The closest I get to something that actually produces values in the pivot table is this:

       

      =sum({<KeyAutoNumber *= {"=(Item_Rep1 = Rep1_Item)"}, KeyAutoNumber *= {"=(BL_Rep1 = Rep1_BL)"}, KeyAutoNumber *= {"=(Ent_Rep1 = Rep1_Ent)"} > } [Val_Rep1])

       

      This provides values, but it calculates all values for all items regardless of the relevance in the report-parameters per table-line

      So progress, but not selective enough.

       

       

      Thanks for any help to get this simple formula to work.

       

      Bart