topic Setanalysis to reflect the Values of Other Rows in QlikView

Setanalysis to reflect the Values of Other Rows

maneshkhottcpl — Sat, 15 Oct 2011 04:30:46 GMT

Hi All,

I have one issue regarding to the set ananlysis (or may not be).

In my data I have two PRODUCT_CLAS, One in MOTOR and another is MOTOR_TP, please refer attached document.

The another column is TP (Amount), What i need in this application is,

While PRODUCT_CLASS is MOTOR_TP -- The chart (Column TP in attachment) should reflect the TP amount from MOTOR class but Zone wise.

Anybody can help me to fix this issue?

Thanks in advance

Manesh

Setanalysis to reflect the Values of Other Rows

swuehl — Sat, 15 Oct 2011 16:29:53 GMT

Hi maneshkhot,

I think you need to add the Zone field to the total qualifier in your sum, like

=if(PRODUCT_CLASS='MOTOR-TP',Sum({$<PRYear={$(=VY)},PRMonthNo={$(=VM)},PRODUCT_CLASS={'MOTOR'}, Zone=p({$<PRODUCT_CLASS={'MOTOR-TP'}>} Zone)>} total<Zone> TP))/100000

Regards,

Stefan

Setanalysis to reflect the Values of Other Rows

maneshkhottcpl — Mon, 17 Oct 2011 15:41:31 GMT

Hi Swuelh

Great answer, It is very helpful to me .

Just explain me that what is total<Zone> used in expression.

Thanks again

Manesh

Re: Setanalysis to reflect the Values of Other Rows

maneshkhottcpl — Mon, 17 Oct 2011 15:42:12 GMT

Hi Swuelh

Great answer, It is very helpful to me .

Just explain me that what is total<Zone> used in expression.

Thanks again

Manesh

Re: Setanalysis to reflect the Values of Other Rows

swuehl — Mon, 17 Oct 2011 15:58:11 GMT

Sure, I just cite the Help (from chart aggregation functions page) for that:

If the word total occurs before an expression, the calculation will be made over all possible values given the current selections, but disregarding the chart dimensions.

The total qualifier may be followed by a list of one or more field names within angle brackets. These field names should be a subset of the chart dimensions. In this case the calculation will be made disregarding all chart dimensions except those listed, i.e. one value will be returned for each combination of field values in the listed dimension fields.

So, in your example, you want to disregard the PRODUCT_CLASS dimension, but not the ZONE dimension.