4 Replies Latest reply: Mar 19, 2012 11:02 PM by cclcjr14 RSS

    How to Change the value of "partial sums" but keep measure's aggregate function

      sc.JPG

      The attached screen shot is a sample report provided by QV, you can easily find it on "start page".

      QlikView provides a function named as "show partial sums", by this way, user can easily append "sum()"/"avg()" /etc at the end of dimension list. For more detail, pls refer to the picture, where is highlighted in red.

      But, whether the "partial sums" value use sum() or avg() is not controlled by user. It is based on the aggregate function of measure. Here it is based on the expression of "Sales EUR 2009".

      Since the expression of this measure is “Sum({$<Year={$(=Max(Year))}>}  Sales / $(vCurrency))”, so the total value is 937,592. The agg function of total is sum().

      But if I change the expression as "avg({$<Year={$(=Max(Year))}>}  Sales / $(vCurrency))", the total value will be 474. At this moment, the agg function of total is changed to avg().

       

      So that bring an issue, when I want to keep the expression of measure as “Sum({$<Year={$(=Max(Year))}>}  Sales / $(vCurrency))”, how can I show 474 (avg value append at the end of dimension list)?

       

      Is it a limitation of QlikView, or QV has any solution to handle such requirement?