Pivottable Totals

[MBV]

Hey Guys!

I've got a Problem with my pivottable.

There is data until YTD (ist) and for the rest a forecast is calculated (prog).
Now I want to combine these columns (kombi).

The problem, as you can see, is that the total is not calculated correctly. Only the ist-valus are considered although the rows are correct.

Does anybody know a solution?

Thanks in advance!!

[lironbaram]

hi, 

you need to do something like 

sum(aggr(if(sum(ist)>0,sum(ist),sum(prog)),Monat,Elnkaufe...))

[MBV]

I'm already using Sum(Aggr()) in my expressions

[Kushal_Chawda]

What is the expression you are using?

[Kushal_Chawda]

try to add  field in aggr which is used as column display in pivot

[MBV]

How would you adjust this Expression using Sum(Aggr())?

if(Sum({< Jahr={$(YTDYear)} >}[h/VK])= '0' ,

(Sum({< Jahr={$(VJ)} >}[h/VK])+ Sum({< Jahr={$(VVJ)} >}[h/VK])+ Sum({< Jahr={$(VVVJ)} >}[h/VK]))/3

,Sum({< Jahr={$(YTDYear)} >}[h/VK]))

[Brett_Bleess]

The following Design Blog post may be of help on this one:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

@Kushal_Chawda If you want to have another look, the poster did post the expression...

Regards,
Brett

[Kushal_Chawda · Accepted solution]

@MBV 

sum(aggr(if(Sum({< Jahr={$(YTDYear)} >}[h/VK])= '0' ,

(Sum({< Jahr={$(VJ)} >}[h/VK])+ Sum({< Jahr={$(VVJ)} >}[h/VK])+ Sum({< Jahr={$(VVVJ)} >}[h/VK]))/3

,Sum({< Jahr={$(YTDYear)} >}[h/VK])),

Monat,Elnkaufe))

Note: If you have other dimensions, add that as well in aggr function

[MBV]

It's working!! Thank you!