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

[#1]

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?

[perumal_41]

Hi,

If Possible Upload Ur Sample Application. I help to U.

Regards,

Perumal A

[jagan]

Hi,

I think it is not possible in Qlikview.  Instead you can display the column total values in separate widget or Textboxes below the column.

Regards,

Jagan.

[#4]

You can easily find this sample report in QV Example folder. I have mentioned this in the picture.

[#5 · Accepted solution]

Finally I found this function, but it is only workable for straight table, instead of pivot table.

click on property-->expression-->total mode, choose the 3rd one, then you will be able to switch aggregation mode for total value.

But, you can only choose the provided function, instead of input any customized expression which is different from default expression.