topic Re: Totals on pivot in QlikView

Totals on pivot

Shiva123 — Mon, 25 Sep 2017 16:50:32 GMT

hi,

everybody i am trying to get to totals on pivot table, i enabled partial sum but still it is not displaying, but it is displaying when i convert it to straight table and i even tried the aggr function with all the dimensions in the expression due to complexity of the expression the dashboard is hanging up i even tried creating the pivot table from scratch it is not working. So what could be the best way to get totals .

Thanks in advance

Re: Totals on pivot

Anil_Babu_Samineni — Mon, 25 Sep 2017 16:52:56 GMT

Please show the expression?

Sum(Aggr(your expression, Dim1, Dim2..))

Re: Totals on pivot

Shiva123 — Mon, 25 Sep 2017 16:55:05 GMT

thanks anil i did that , that hangs up my dashboard due to complexity of expression ( nested if ).

thanks

Re: Totals on pivot

sunny_talwar — Mon, 25 Sep 2017 16:56:03 GMT

Can you share how the expression looks like?

Re: Totals on pivot

Shiva123 — Mon, 25 Sep 2017 16:58:58 GMT

hi sunny attached is the screen shot of the expression

Re: Totals on pivot

sunny_talwar — Mon, 25 Sep 2017 17:03:47 GMT

This expression can be drastically simplified for 2 reasons...

1) You can probably avoid using Aggr(NODISTINCT, Month, Period_YYYY) using TOTAL <Month, Period_YYYY>

2) You are using the same Aggr() every time, which means you can use an overarching Aggr() instead of repeating it 100 times

Re: Totals on pivot

Shiva123 — Wed, 27 Sep 2017 17:35:12 GMT

hi sunny my concern is as the expression is running through a loop will the total function do the same as aggr(no distinct and one more thing is there any chances that this could be a bug in qlik ?.

thanks

Re: Totals on pivot

sunny_talwar — Wed, 27 Sep 2017 18:31:42 GMT

Aggr(NoDISTINCT ... ) is not exactly the same thing as TOTAL... but Aggr(NODISTINCT ....) can very often be replaced with TOTAL Qualifier.... In your case, I cannot guarantee it will work... but if it does... you will see a lot of performance imporvement

I am not sure what are you referring to as a bug?

Re: Totals on pivot

Shiva123 — Wed, 27 Sep 2017 18:37:26 GMT

i am referring to the totals on pivot .Because i was able to get totals on one of the pivot table where as i am not able to get here

Re: Totals on pivot

sunny_talwar — Wed, 27 Sep 2017 18:41:38 GMT

I don't think that this is a bug... I think this is by design... you can overcome your problem by using a straight table and selecting 'Sum of Rows' and I think that should fix your problem... but I am not sure if I can guide you in fixing this in pivot table...

You can try...

Sum(Aggr(Your Expression, YourDimensions))

Re: Totals on pivot

Shiva123 — Wed, 27 Sep 2017 18:49:31 GMT

i tried with the straight table and it was working fine . but the view which is generated by the straight table is not the thing which users expect . i even tried by making the dimensions as horizontal in straight table , but in straight table all the dimensions are moving to horizontal instead of one dimension . i even used the function Sum(Aggr(Your Expression, YourDimensions)) as you know it slows down the apps as it has many aggr functions in the expression . is there any possible solution for this .

Re: Totals on pivot

sunny_talwar — Wed, 27 Sep 2017 18:52:29 GMT

is there any possible solution for this .

Already told you that you need to simplify your expression.... not sure what else you want me to tell you

Re: Totals on pivot

Shiva123 — Wed, 18 Oct 2017 16:34:29 GMT

hey sunny i did change the expression to , total<month, period_yyyy> but still i am not getting totals .. the performance of dashboard is better when i changed it to total rather than aggr(no distinct )

Re: Totals on pivot

Sergey_Shuklin — Wed, 18 Oct 2017 16:49:26 GMT

Hello, Shiva!

I'd faced such problem not so far when I start using QV12. When I closed my project and opened it again the partial sums had appeared, so it may be a bug of 12 version.