topic Pivot issue in QlikView

Pivot issue

Wed, 04 Jul 2012 12:47:14 GMT

I have an issue with a pivot I just did (Im a newbie at qlikview)

I just did this pivot that shows the subscription renewal count for each of our products. Now at the end (next to 13) I want to add the average count for each product and maybe also the average customer lifetime value(it should be average count * product price).

But How do I add that at the end of the pivot?

Re: Pivot issue

swuehl — Wed, 04 Jul 2012 12:57:16 GMT

You can enable a partial sum for you second dimension in presentation tab of your chart properties, the use as expression something like

=if(SecondaryDimensionality()=0,avg(aggr( count(FIELD),product_id,c)),count(FIELD))

i.e. use secondarydimensionalty() function to check if your in a cell or the total column, then use an appropriate expression (in my example, you need to replace the aggr() dimensions with your chart dimensions and FIELD with the field you are counting on.

Hope this helps,

Stefan

Re: Pivot issue

Wed, 04 Jul 2012 13:26:45 GMT

I dont understand what I need to replace "aggr()" with?

Re: Pivot issue

swuehl — Wed, 04 Jul 2012 13:37:41 GMT

No, you probably need the advanced aggregation (to average the counts per your second dimension).

I intened to say that you need to reuse your pivot table chart dimensions (product_id and c ?) also as dimensions in your aggr() function. So if your field names you are using as dimension in the pivot table are product_id and c, you don't need to change the aggr() dimensions. Just replace count(FIELD) with the expression you are using to calculate the counts. in your table.

Re: Pivot issue

Thu, 05 Jul 2012 09:26:28 GMT

I Tried to add "=if(SecondaryDimensionality()=0,avg(aggr( count(count),product_id,c)),count(count)) in the "Label for Totals" under partial sums, but nothing really happened - it still showing the total numbers.

(Count is the name of the field)

Re: Pivot issue

swuehl — Thu, 05 Jul 2012 09:32:33 GMT

I assume you are currently using one expression (defined in expression tab) to calculate your cell values in your pivot table.

As I understood, this expression is

=count(count)

Replace this expression by

=if(SecondaryDimensionality()=0,avg(aggr( count(count),product_id,c)),count(count))

And don't forget to enable the partial sum for your second dimension.

The secondarydimensionality() is used for dimensions dragged to the top (as in your sample image). Use dimensionality() if you are using vertical dimensions.

Re: Pivot issue

Thu, 05 Jul 2012 09:47:00 GMT

Ohh Thank you! - I tried to change the expression, but the only thing which happened were that totals columns disappeared

Re: Pivot issue

swuehl — Thu, 05 Jul 2012 09:56:22 GMT

Ok, so your second dimension is not called 'c' but 'count', right?

Please check my above posts and correct the second dimension in the aggr() function from 'c' to 'count':

=if(SecondaryDimensionality()=0,avg(aggr( count(count),product_id,count)),count(count))

Re: Pivot issue

Thu, 05 Jul 2012 10:14:59 GMT

Got it to work - Thank you swuehl!

But I noticed it calculates the wrong average:

And I dont really understand how to change it?

What I would like to calculate is that average subscription count for a product_id:

For product_id 23 it should calculate:

=((2954*1)+(2342*2)+(1924*3)+(1578*4)+(1368*5)+(1155*6)+(992*7)+(829*8)+(718*9)+(643*10)+(531*11)+(360*12)+(53*13)) / (2954+2342+1924+1578+1368+1155+992+829+718+643+531+360+53)

Average count = 4,6

Re: Pivot issue

swuehl — Thu, 05 Jul 2012 11:09:40 GMT

So maybe

=if(SecondaryDimensionality()=0,

sum(aggr( count*count(count),product_id,count)) / sum( aggr( count(count), product_id,count)),

count(count))

[And please try to format your total results in number tab, currently it's quite hard to check if the results are ok.]

Re: Pivot issue

Thu, 05 Jul 2012 12:49:23 GMT

Swuehl - you are my hero!

I tried to format the numbers, but the average count and the standard count should be formated differently, so it's hard to get right.

Re: Pivot issue

swuehl — Thu, 05 Jul 2012 12:53:15 GMT

Ah, I see.

Instead of the number format tab, try using the num() function around your calculation expressions (setting the number tab format to 'expression default'):

=if(SecondaryDimensionality()=0,

num(sum(aggr( count*count(count),product_id,count)) / sum( aggr( count(count), product_id,count)),'###.00'),

count(count))

)

Re: Pivot issue

Thu, 05 Jul 2012 13:07:31 GMT

Can't I just add you to Skype - that would SO great 🙂

Thank's for your help - I would have never figured this out by myself... Now I just need to figure out what the expression means, so actully will learn something 😄