Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
gf
Creator III
Creator III

Sum calculated dimension

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. 

 

Labels (4)
1 Solution

Accepted Solutions
gf
Creator III
Creator III
Author

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;


View solution in original post

19 Replies
zhadrakas
Specialist II
Specialist II

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

 

gf
Creator III
Creator III
Author

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.
gf
Creator III
Creator III
Author

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

@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))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anil_Babu_Samineni

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
zhadrakas
Specialist II
Specialist II

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
gf
Creator III
Creator III
Author

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
zhadrakas
Specialist II
Specialist II

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

May you have a look at my sample data.