topic Re: Conditional sum in a pivot table in QlikView

Conditional sum in a pivot table

juan_c_martinez — Fri, 20 Dec 2013 00:21:54 GMT

I want to sum the highest level values (in grey) in the pivot table table:

I have tried several strategies with set analysis,, aggr event with if.... none has worked.. (see qvw attached)

Any suggestion?

Thanks in advance....!!!

Juan C.

Re: Conditional sum in a pivot table

jagan — Fri, 20 Dec 2013 03:22:03 GMT

Hi,

Arrive a flag in script like this

Data:

LOAD Dim1,

Dim2,

Month1,

Level,

Value

FROM

Sun_Max.xlsx

(ooxml, embedded labels, header is 1 lines, table is Hoja3);

LEFT JOIN

LOAD

Dim1,

Dim2,

Month1,

Max(Level) AS Level,

1 AS Flag

RESIDENT Data

GROUP BY Dim1,

Dim2,

Month1;

Now use this expression to get only max Level values

=Sum({<Flag={1}>} Value)

Regards,

Jagan.

Re: Conditional sum in a pivot table

juan_c_martinez — Fri, 20 Dec 2013 13:05:14 GMT

Jagan,

Thanks for your time!!!

This solution works fine in case of the Dim1 and DIm2 were fixed. But the solution I look for should work for any combination of dimensions (in the real scenery are 5 dimensions and the user always wants to see by any combination of dimension the sum of the cases with highest level of priority ).

The solution should came using set analysis in combination with the dolar-sign expansion but unfortunately the function inside the dolar-sign is evaluated outside the pivot table and then it doesn't take in account the level according to the combination of dimensions existing in the cell.

Again thanks,

Juan