topic Re: Totals in pivot table after calculations/aggregation in script in QlikView

Totals in pivot table after calculations/aggregation in script

Fri, 11 Nov 2016 14:40:53 GMT

Dear Colleagues,

I have made some calculations in the script (unique for each line, so no further aggregations are needed) to avoid calculations in the frontend.

Now I am showing this Field in a pivot table under Expressions without any functions at all. Works fine, BUT Totals and Subtotals are not shown, unless I select only one distinct value.

Question: How can I (in a pivot table) show a field without any aggregation functions and get the totals?

Thank you.

Vitalii

Re: Totals in pivot table after calculations/aggregation in script

sunny_talwar — Fri, 11 Nov 2016 16:48:15 GMT

May instead of using FieldName, try using Sum(FieldName) to get the subtotals

Re: Totals in pivot table after calculations/aggregation in script

sunny_talwar — Fri, 11 Nov 2016 16:49:00 GMT

Or are you trying to avoid even using Sum()? I mean if your total is Sum(FieldName), how else would you get Sum(FieldName) without using a Sum() function?

Re: Totals in pivot table after calculations/aggregation in script

johnw — Fri, 11 Nov 2016 18:11:04 GMT

If it were a straight table we could do sum of rows, but not in a pivot table. I can't think of any way to do a sum in a pivot table without using sum(). Why are you trying to avoid using aggregation functions?

Re: Totals in pivot table after calculations/aggregation in script

Mon, 21 Nov 2016 16:33:13 GMT

Yes, exactly. I am trying to avoid any calculations in the front end in order o make the application „lighter“. That is why I moved sum() to the script (with group by respectively) and now try use the fore calculated values. But using just field value without any aggregation doesn’t allow me see totals on the top of the table….

Re: Totals in pivot table after calculations/aggregation in script

Mon, 21 Nov 2016 16:33:48 GMT

I am trying to avoid any calculations in the front end in order o make the application „lighter“. That is why I moved sum() to the script (with group by respectively) and now try use the fore calculated values.

Re: Totals in pivot table after calculations/aggregation in script

johnw — Mon, 28 Nov 2016 19:46:11 GMT

So you just want the grand totals to display as well?

You could maybe load one more row onto your table in script with a key value like 'total' and the grand totals in it.

But I suspect you're going in the wrong direction. Doing aggregations in script is usually the very last solution I would ever try to address front end performance problems. I would usually try everything else before I resorted to that, because resorting to that just throws out the window how selections are supposed to work, or at least the ability to drill into details. That does suggest one exception to my rule, which would be for non-interactive or very limitedly interactive dashboards. In a few cases I have indeed made "light" dashboards by aggregating in script.

And performance has forced me to aggregate in script once. No matter what I tried, I couldn't get a particular chart to render faster than about half a minute, and that just wasn't acceptable. So I'm not saying that script aggregation is taboo or illegal or immoral or anything, just that there's almost always a better solution. One strength of QlikView is the speed of its front end aggregation. If you're having serious performance problems, there's a very good chance there's a better way to solve those problems than to aggregate in script.

Re: Totals in pivot table after calculations/aggregation in script

Tue, 29 Nov 2016 08:40:10 GMT

Dear John,

thank you very much! It is a good food for thoughts!:)

Vitalii