topic Re: Sum calculated dimension in QlikView

Sum calculated dimension

gf — Sat, 16 Nov 2024 21:00:00 GMT

Hello all,

is it possible to display subtotals for calculated dimension in a pivot table.

Or is there any other solution with straight table to achive this outcome.

For calculated dimension Betrag_Abgrenzung, Betrag Bilanzbuchung, Differenz and Veränderung should the subtotals be displayed.

Re: Sum calculated dimension

zhadrakas — Wed, 24 Apr 2019 09:49:24 GMT

Hello,

I think you are using complex Expression here. That should be the reason why there is no subtotal.

i would recommend to wrap an sum(aggr(Your_Expression, your_Dimension)) around it.

regards

tim

Re: Sum calculated dimension

gf — Wed, 24 Apr 2019 09:57:21 GMT

I tried
=SUM(AGGR(NUM(BETRAG_ABGRENZUNG,'##.###,##'),UK,SK,SUBKATEGORIE,ZE,ZNR,KOST,BK,KOA,J,N,U,SV_ID,C,T,AEK,K,X,M,BETRAG_ABGRENZUNG))
as calculated dimension.
But i get an error.

Re: Sum calculated dimension

gf — Wed, 24 Apr 2019 09:59:44 GMT

Also
=SUM(AGGR(SUM(DISTINCT BETRAG_ABGRENZUNG),BETRAG_ABGRENZUNG))
doesn't work.

Re: Sum calculated dimension

Anil_Babu_Samineni — Wed, 24 Apr 2019 10:00:19 GMT

@gf Perhaps this?

SUM(AGGR(NUM(Sum(BETRAG_ABGRENZUNG),'##.###,##'),UK,SK,SUBKATEGORIE,ZE,ZNR,KOST,BK,KOA,J,N,U,SV_ID,C,T,AEK,K,X,M,BETRAG_ABGRENZUNG))

Re: Sum calculated dimension

Anil_Babu_Samineni — Wed, 24 Apr 2019 10:01:30 GMT

Since it is calc. dimension - Sum() never work. Perhaps this?

AGGR(NUM(Sum(BETRAG_ABGRENZUNG),'##.###,##'),UK,SK,SUBKATEGORIE,ZE,ZNR,KOST,BK,KOA,J,N,U,SV_ID,C,T,AEK,K,X,M,BETRAG_ABGRENZUNG)

Re: Sum calculated dimension

zhadrakas — Wed, 24 Apr 2019 10:38:48 GMT

I would Keep the Expression like this but add it as Expression and not as dynamic Dimension:
You will not get the subtotals in dynamic dimensions. Only in Expressions.
=SUM(AGGR(NUM(BETRAG_ABGRENZUNG,'##.###,##'),UK,SK,SUBKATEGORIE,ZE,ZNR,KOST,BK,KOA,J,N,U,SV_ID,C,T,AEK,K,X,M,BETRAG_ABGRENZUNG))
Repeat this for the other 3 dynamic dimensions

Re: Sum calculated dimension

gf — Wed, 24 Apr 2019 11:04:52 GMT

Thanks for your answers!
@tim the problem is i want the columns to stay in their original position. As far as i know expression column will only show at the end of a pivot table.

@Anil_Babu_Samineni your expression works but subtotals won't display

Re: Sum calculated dimension

zhadrakas — Wed, 24 Apr 2019 11:09:16 GMT

You are right. thats not possible in Pivot table.
But in straight table you can do that

Re: Sum calculated dimension

gf — Wed, 24 Apr 2019 11:10:55 GMT

May you have a look at my sample data.

Re: Sum calculated dimension

gf — Wed, 24 Apr 2019 11:11:46 GMT

Is it possible to show subtotals in straight table?

Re: Sum calculated dimension

zhadrakas — Wed, 24 Apr 2019 11:17:56 GMT

ahh there we go again. no it's not. Just one total row.

I think you're only Chance (maybe there is a workaroung with a lot of work) is to Change the dynamic dimensions to Expression as i said before:

regards

tim

Re: Sum calculated dimension

gf — Wed, 24 Apr 2019 11:25:50 GMT

Thank you tim for your time and consideration. As we say it in german, "Wenn alle Stränge reißen", i will try it as you recommend. Firstly i will (hopefully) find/search a solution where i can solve this problem diretly in the LOAD.

Regards
Gf

Re: Sum calculated dimension

zhadrakas — Wed, 24 Apr 2019 11:31:45 GMT

yes that is definitely a better solution. Just to make it faster and avoid using dynamic Expressions.
But you will still have the Problem that you can't Show subtotals on dynamic dimensions.

Re: Sum calculated dimension

Anil_Babu_Samineni — Wed, 24 Apr 2019 11:53:04 GMT

Subtotal never works as i said before in dimension level.

Re: Sum calculated dimension

zhadrakas — Thu, 25 Apr 2019 09:37:03 GMT

NOTE: after thinking a bit you can calculate your own Subtotals in script like
outer join Load
"TOTAL" as SUBKATEGORIE
sum(BETRAG_ABGRENZUNG) as BETRAG_ABGRENZUNG,
sum(BETRAG_2) as BETRAG_2,
...
Resident YOUR_TABLE
GROUP BY "TOTAL"
;

Re: Sum calculated dimension

gf — Thu, 25 Apr 2019 11:01:31 GMT

Thanks for your answer
I managed to get the right output with the following LOAD.

Ueberleitung:
LOAD ...
Num#(BETRAG_ABGRENZUNG,'#.###,##') as BETRAG_ABGRENZUNG,
Num#(BETRAG_BILANZBUCHUNG,'#.###,##') as BETRAG_BILANZBUCHUNG,
Num#(DIFFERENZ,'#.###,##') as DIFFERENZ,
Num#(VERAENDERUNG_DIFFERENZ,'#.###,##') as VERAENDERUNG_DIFFERENZ;
SQL Select....;

Concatenate(Ueberleitung)

UeSummen:
LOAD ...
SUM(Distinct BETRAG_ABGRENZUNG) as BETRAG_ABGRENZUNG,
SUM(Distinct BETRAG_BILANZBUCHUNG) as BETRAG_BILANZBUCHUNG,
SUM(Distinct DIFFERENZ) as DIFFERENZ,
SUM(Distinct VERAENDERUNG_DIFFERENZ) as VERAENDERUNG_DIFFERENZ
Resident Ueberleitung
Group by LAND, FCO, STICHTAG, UK;

Re: Sum calculated dimension

gf — Thu, 25 Apr 2019 11:02:36 GMT

Is it possible that i can not format the cell values because of the CONCATENATE?

Re: Sum calculated dimension

zhadrakas — Thu, 25 Apr 2019 11:06:50 GMT

for concatenate to work correctly both tables should have the exact same columns.
!! Really think about using sum(Distinct ???)
!! You only sum each value one time (sum Distinct of {1;1}) = 1

Re: Sum calculated dimension

gf — Thu, 25 Apr 2019 11:10:13 GMT

The tables have the same columns. I just didn't mentioned them because there are round about 20.
The reason why i used DISTINCT was that sum doubles all my values and i don't know why, with DISTINCT the values are right.