topic Re: Pivot table - partial sum in QlikView

Pivot table - partial sum

Thu, 18 Dec 2014 13:48:29 GMT

Hello.

I have constructed a pivot table and have set up a partial sum to calculate my total column

My expression is an average of my headcount. However, for my TOTAL column also works the average out.

So in the example below, instead of having 8.77 as a total, I want 16.50 + 49.4 + 2.10 + 8.00 + 3.00 = 79

How can I acheive this?

Many thanks

Re: Pivot table - partial sum

Thu, 18 Dec 2014 13:59:54 GMT

Please post your expression and some sample data if possible. Without knowing more, I assume that some aggregation function (for example sum) is missing from your expression. Try adjusting that, then let me know what happened.

Best,

Re: Pivot table - partial sum

Thu, 18 Dec 2014 14:05:06 GMT

Hi Tibor,

my expression reads as '=Aggr(Only({1< Entity = P({1< Region={'ASIA'}>} Entity) >} Entity), Region, Entity)'

I have tried encasing it within the sum expression but it didn't quite work.

Re: Pivot table - partial sum

Thu, 18 Dec 2014 14:16:22 GMT

And you say that per dimensions the expression gives you correct values, but when subtotaling, the number you get is not the sum of averages, but the average of averages? It would be much of help if you could post a picture with your table and data.

Re: Pivot table - partial sum

Thu, 18 Dec 2014 14:28:21 GMT

Ok , attached is an example, so instead of getting 12.5, I want 11+12+13+14 = 50

Re: Pivot table - partial sum

Thu, 18 Dec 2014 14:37:00 GMT

If you are not insisting on using a pivot table, you can achive waht you want by changing the type to straight table, then on the expression tab, checking sum of rows.

Re: Pivot table - partial sum

Thu, 18 Dec 2014 14:39:52 GMT

Yes I agree that this can be done in a table. However, I need to use the pivot table. In my app where my real data resides, a table isn't sufficient in terms of the layout as it is a Profit and Loss piece. Hence why I want to use a pivot.

Re: Pivot table - partial sum

Thu, 18 Dec 2014 14:45:45 GMT

Try this in your expression:

if(dimensionality()<>'1',sum(Value),avg(Value))

Let me know whether it worked or not.

Re: Pivot table - partial sum

Thu, 18 Dec 2014 14:59:37 GMT

Hi Tibor,

Yes in the example I gave you, its worked perfectly.

I need to figure out why it isn't working in my app now!

Thank you!!

Re: Pivot table - partial sum

Thu, 18 Dec 2014 15:05:42 GMT

I think an expression like that should also work in your app. Maybe you wrote the expression incorrectly? What do you get now as a result?

Re: Pivot table - partial sum

Thu, 18 Dec 2014 15:27:56 GMT

Hey,

So when I apply the same expression, it makes no difference in my application

I am encasing it within another IF statement:

IF(PLLineSort='HEADCOUNT (FTE)',

IF(Dimensionality() <> '1',

num(sum({< TransactionType = {'PL'}, Type={'Actual'}, CAT={'Heads'}, MN_Num = {">=$(vReportingYearStart)<=$(vReportingDate)"} >}[Value EUR]), 0.00),

num(avg({< TransactionType = {'PL'}, Type={'Actual'}, CAT={'Heads'}, MN_Num = {">=$(vReportingYearStart)<=$(vReportingDate)"} >}[Value EUR]), 0.00)),

It still gives me the average at the total level