topic Yet another Pivot Total issue in QlikView

Yet another Pivot Total issue

Tue, 06 Apr 2010 13:29:22 GMT

Same old issue: Total value in the pivot below is obviously wrong.

But in this case, my dimensions are cyclic groups and my expression is actually a variable containing a sum(), because I want to use it in different objects. So, I couldn't figure out a working aggr() expression. Any ideas?

Generally speaking, I don't like this behaviour...

Thanks

Yet another Pivot Total issue

Tue, 06 Apr 2010 15:51:44 GMT

Paulo

Not sure how much of your expression is in the variable, but what I tend to do is to put the repeatable part in the variable and then wrap the Aggr function around it, this way you can use the variable in different objects.

Example:

Normal formula is =Sum ( Aggr ( Sum ( ValueField ) , Dim1, Dim2 ))

Create variable (vNewVar) that contains =Sum(ValueField)

Then change your expression to:

=Sum ( Aggr ( $(vNewVar ) , Dim1, Dim2 ))

Will this work for you?

Yet another Pivot Total issue

Tue, 06 Apr 2010 16:16:33 GMT

Yes, it works with some change: the problem here (guess) is that my two dimensions are not enough to uniquely distinguish the values (in the example I added the rownumber dimension but I don't want to display it). Therefore, I created an unique row ID for my dataset in the script and I modified my variable like that:

vNewVar = sum(aggr(sum(ValueField), ID))

Then I simply use $(vNewVar) in my expressions.Not sure it is the correct approach, but it seems to be working everywhere....

By the way, there is no need for this workaround within linear tables and "sum of rows" option. This behaviour is not straightforward to understand....

Thanks

Paolo