Sum calculated dimension

[gf]

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. 

[gf]

Is it possible to show subtotals in straight table?

[zhadrakas]

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:

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

regards

tim

[gf]

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

[zhadrakas]

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.

[Anil_Babu_Samineni]

Subtotal never works as i said before in dimension level.

[zhadrakas]

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"
;

[gf · Accepted solution]

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;

[gf]

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

[zhadrakas]

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

[gf]

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.