topic Re: Problem with total sum in pivot formula in QlikView

Problem with total sum in pivot formula

Tue, 02 Jul 2013 06:41:11 GMT

Hi,

I don't find any solution for my problem with pivot total sum from existing discussions. I need to count staff expenses and my formula consists three different part of costs.

a. ((sum(salaries_from_database)) +

b. ((sum(ROOMS_from_database) / sum(Multiplier_from_excel))*24) +

c. (sum(Multiplier_from_excel)*sum(EuroAmount_from_excel))

If I look at this in monthly view the formula works as it totals a. and b. correctly but from c. only one value e.g.

Multiplier = 2 and Euro = 10, total is 20 in every case, even if I have chosen monthly or daily view.

e.g. total should be 31 (month) x 20 = 620, but it counts only 20. I have this same c. formula in other table too and there I was able to get the right result by choosing sum of the rows instead of Expression total. But that does not work in Pivot table and because I have three different part in my staff expenses formula.

I would be very grateful if someone could help me with this. Thank you!

sum(total fieldname) does not help.

-Maiq-

Re: Problem with total sum in pivot formula

brijesh1991 — Tue, 02 Jul 2013 08:42:59 GMT

Hi,

I was facing the same problem for quite long time, also reported to QlikTech.

My problem resolved dramatically. Just create the same pivot table again(new object). . .

Hope your case might be resolved. . .

Re: Problem with total sum in pivot formula

Tue, 02 Jul 2013 10:18:51 GMT

Hi,

Thanks for your quick answer. I tried to create a new pivot table, but unfortunately that didn't help.

I also tried count() -function as Count(Day) * sum(Multiplier_from_excel * EuroAmount_from_excel) which seems to work until I find the right solution.

Re: Problem with total sum in pivot formula

er_mohit — Tue, 02 Jul 2013 10:27:27 GMT

have you tried using straight table

go to expression tab on straight table

for that particular expression you ll see on right side total mode with radio button

tick on 3rd one where you select drop down total count of rows

hope it helps

Re: Problem with total sum in pivot formula

brijesh1991 — Tue, 02 Jul 2013 11:22:33 GMT

Hey. . .

In your pivot table properties,

Go to presentation tab--> select the dimention for which you wanted to aggregate the above expressions, check the 'show partial sum' checkbox. . .

Hope it will work in your case. . . (Y)

Re: Problem with total sum in pivot formula

Tue, 02 Jul 2013 12:04:15 GMT

Hi, thanks, it works better in straight table. But still I need to use count() function.