topic Re: How to calculate with pivot table in QlikView

How to calculate with pivot table

Thu, 19 Dec 2013 11:13:09 GMT

Hi all.

I have a pivot table with to calculations that doesen't give me the right value.I have the right values for every row when I expand the table but when the table is collapsed the "total" is wrong. Se example below:

Expression for num days:

count({$<Salary_ID={'col'}, Absence_ID={'vac'}>} DISTINCT Start_Date)

Expression for Value:

(count({$<Salary_ID={'col'}, Absence_ID={'vac'}>} DISTINCT Start_Date))*(sum({$<Salary_ID={'col'}, Absence_ID={'vac'}>} Unit_Price))

Suggestions to solve this?

Re: How to calculate with pivot table

Gysbert_Wassenaar — Thu, 19 Dec 2013 11:19:24 GMT

Well, technically the totals are correct. You did ask for distinct start dates. Perhaps you want something like this instead: sum(aggr(count({$<Salary_ID={'col'}, Absence_ID={'vac'}>} DISTINCT Start_Date), Account_..., Home_CC,Employee_ID))

Re: How to calculate with pivot table

Thu, 19 Dec 2013 11:31:26 GMT

Perfect! But how to solve the next expression for the column Value?

(count({$<Salary_ID={'col'}, Absence_ID={'vac'}>} DISTINCT Start_Date))*(sum({$<Salary_ID={'col'}, Absence_ID={'vac'}>} Unit_Price))

Re: How to calculate with pivot table

Thu, 19 Dec 2013 12:01:37 GMT

Thanks a lot Gysbert for your reply. I solved the second question I had.