8 Replies Latest reply: Oct 11, 2015 2:04 PM by romina fratoni RSS

    Challenge with Pivot Table Total

      Hi all,

      probably I'm not so expert with Qlikview but I have a big issue in a pivot table to calculate a total when I filter for a dimension.

      Following the example:

       

      PIVOT_EXAMPLE.JPG

       

      In this example the column 'Product CR' is calculated as :

      [Qty Upsell]/SUM(TOTAL <MAIN_PRODUCT_UPSELL> {$<ACTION = {"I"}, MAIN_ORDER = {"1"}, WEB_CHANNEL = {"0"}>}QTY)

      Final user would like to filter only one upsell product and see the correct 'Product CR'; with the previous formula if I select only one row of upsell , the formula calculates only the total quantity for that upsell product.

      It would be appreciate any good solution.

       

      Thanks,

      Romina

        • Re: Challenge with Pivot Table Total
          Stefan Wühl

          Try

          [Qty Upsell]/SUM(TOTAL <MAIN_PRODUCT_UPSELL> {1<ACTION = {"I"}, MAIN_ORDER = {"1"}, WEB_CHANNEL = {"0"}>}QTY)

           

          using set identifier 1 or


          [Qty Upsell]/SUM(TOTAL <MAIN_PRODUCT_UPSELL> {$<ACTION = {"I"}, MAIN_ORDER = {"1"}, WEB_CHANNEL = {"0"}, UPSELL_PRODUCT= >}QTY)

            • Re: Challenge with Pivot Table Total

              Hi swuehi,

              thank you so much.

              Yes of course it works with the second option, but actaully I have another issue.

              When I filter only one upsell product the 'Product CR' is correctly calculated but the other columns are filtered and the total is not correct.

              So I'm trying to think how to fix the requirement because the objective of business is to filter only the main product or only the upsell or both products.

              Do you think that is possible the solution: when I filter an upsell product I see all combinations of main product (related to the upsell product selected) and upsell products.

              I wait for your idea,

              Romina