Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Not applicable

Totals in pivot table after calculations/aggregation in script

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.

BR

Vitalii

7 Replies

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

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

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

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?

MVP
MVP

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

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?

Not applicable

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

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….

Highlighted
Not applicable

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

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.

MVP
MVP

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

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.

Not applicable

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

Dear John,

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

BR

Vitalii