topic Re: Calculated measure in Pivot table? in QlikView

Calculated measure in Pivot table?

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Is it possible to create calculated measure in Pivot table - dimension tab (i'can't put this calculation in expression tab due to my requirement report template restriction).

used below expression in Dimension,

=aggr(SUM(SoldQty),ArticleNo,Batch)

Thanks, Deva

Re: Calculated measure in Pivot table?

adamdavi3s — Tue, 17 Jan 2017 11:57:48 GMT

I'm not sure this is possible.

I was hoping you might be able to get away with faking it using the totals label but it doesn't work because you're limited to the cell width of your dimension so you can't 'shift' the total under the correct column

Re: Calculated measure in Pivot table?

marcus_sommer — Tue, 17 Jan 2017 11:59:54 GMT

You mean you want a total for your calculated dimension within the total-row of Material? I don't believe that this will be possible. A workaround for this might be to include this total within the calculated dimension itself, maybe something like this:

aggr(SUM(SoldQty),ArticleNo,Batch) & ' of ' & aggr(SUM(SoldQty),ArticleNo) & ' in total'

- Marcus

Re: Calculated measure in Pivot table?

devarasu07 — Tue, 17 Jan 2017 18:06:59 GMT

Hi marcus_sommer‌

Thanks for the workaround method, i've tried and it's working only when no dimension kept in pivot table column position. please refer to attached sample mock. kindly advise. Thanks

Re: Calculated measure in Pivot table?

marcus_sommer — Wed, 18 Jan 2017 08:33:10 GMT

The expression for the calculated dimension must be look like:

= aggr(SUM(TotalNetPrice),ArticleNo,Batch) & ' of ' &

aggr(NODISTINCT SUM(total <Batch, SKUTheme> TotalNetPrice),Batch, SKUTheme) &

' in total'

The suggestion from Adam is also very interesting but they will only work if you want to display a global total within because this label-field don't respect the dimensions within the chart.

- Marcus

Re: Calculated measure in Pivot table?

marcus_sommer — Wed, 18 Jan 2017 09:39:02 GMT

In general there would be another possible approach by adding 'TOTAL' fieldvalue(s) within the field ArticleNo and then reacting per if-checks if there is a real ArticleNo or this dummy-value like this one:

if(ArticleNo = 'TOTAL', exp1, exp2)

by removing the partial sums for this field and formatting these rows with attribute expressions. But it will be also only a workaround and cost performance, makes it more complex and need some efforts to implement it.

- Marcus