topic Total of first dimension over second dimension in QlikView

Total of first dimension over second dimension

Wed, 01 Feb 2012 10:57:25 GMT

Hi Friends,

I Need a pivot Table to show the Sub Total of on dimension as follows

Dimension1	Dimension2	Dimension3
A	a	2
	b	2
	Total	4
B	a	3
	b	3
	Total	6
Total	a	5
	b	5
	Total	10

How can in acheive this without using set analysis.

Total of first dimension over second dimension

kaushiknsolanki — Wed, 01 Feb 2012 10:59:24 GMT

HI,

Go to properties -> presentation -> select dimention2 and check " Show partial sum"

Regards,

Kaushik Solanki

Total of first dimension over second dimension

Wed, 01 Feb 2012 11:03:07 GMT

Hi,

Thanks for your answer... I can get the Total Part of the Dimension2 but how am to acheive the Total for Dimension1 as mentioned in the table above

Total of first dimension over second dimension

kaushiknsolanki — Wed, 01 Feb 2012 11:05:47 GMT

Hi,

You can get the over all total for dimension1, with same logic. Just select dimention1 in presentation and check the show partial sum.

Regards,

Kaushik Solanki

Total of first dimension over second dimension

Wed, 01 Feb 2012 11:10:37 GMT

Hi,

Thanks, tried the same i get the result as

Dimension1	Dimension2	Dimension3
A	a	2
	b	2
	Total	4
B	a	3
	b	3
	Total	6
Total	Total	10

Total of first dimension over second dimension

Wed, 01 Feb 2012 11:20:32 GMT

Sorry, I didn't acheive what i asked for.

Total of first dimension over second dimension

kaushiknsolanki — Wed, 01 Feb 2012 11:25:42 GMT

Hi,

You will not get the way you want, cause it will give you overall total for first dimension.

Regards,

Kaushik Solanki

Total of first dimension over second dimension

Wed, 01 Feb 2012 12:25:39 GMT

Hi Bala,

What kaushik said is right. you can't get the sub total for the first dimension like what u would have got for the second dimension by using partial sum. but this s the perfect solution for your problem.

Regards,

Arun Prasadh.N.

Re: Total of first dimension over second dimension

Wed, 01 Feb 2012 13:03:03 GMT

Dear Arun,

Thanks,can i attain it in anyother means.

Regards,

Bala

Re: Total of first dimension over second dimension

swuehl — Wed, 01 Feb 2012 13:19:19 GMT

You could create a new dimension Value for your first dimension, that will hold the total:

INPUT:

LOAD * INLINE [

DIM1, DIM2, Value

A, a, 2

A, b, 2

B, a, 3

B, b, 3

];

LOAD

'TOTAL' as DIM1,

DIM2,

sum(Value) as Value

Resident INPUT group by DIM2;

This should show the exact table that you required, see attached. The values precalculated in the script are not selection sensitive, though. But you could check for the DIM1 value and calculate the Totals on the fly, too, like this

=if(DIM1<>'TOTAL',sum(Value), sum({<DIM1 -= {TOTAL}>} total<DIM2> Value))

Please see attached.

Stefan

Total of first dimension over second dimension

martinpedersen — Tue, 06 Mar 2012 12:57:28 GMT

I would love to do this kind of "aggregated subtotals" - its a basic function in e.g. Cognos - Does anyone know if there's any plans regarding implementation of this option in QV?

Re: Total of first dimension over second dimension

Adam_Smith — Thu, 28 Apr 2022 12:29:47 GMT

I know this is an old thread, but I have found a solution which works well for me so thought I would share it here.

In your load script, you can create a separate table with 2 fields... Dimension1 and also Dimension1Total.

Ensure that for every Dimension1, you have both a Dimension1 and a TOTAL listed in the Dimension1Total field.

i.e. below example load script

FactTable:
LOAD * INLINE [
Dimension1, Dimension2, Value
A, a, 2
A, b, 2
B, a, 3
B, b, 3
];

Dimension1Total:
LOAD * INLINE [
Dimension1, Dimension1Total
A, TOTAL
B, TOTAL
A, A
B, B
];

Qlikview's associative engine would then link this table back to your main table on Dimension1.

Now, in your chart you can use the field Dimension1Total as your first Dimension. The chart will now include a row for TOTAL at the top of the table, and each of the Dimension2 fields will be shown in this TOTAL row.