Setanalysis to reflect the Values of Other Rows

[maneshkhottcpl]

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

[swuehl · Accepted solution]

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

[maneshkhottcpl]

Hi Swuelh

Great answer, It is very helpful to me .

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

Thanks again

Manesh

[maneshkhottcpl]

Hi Swuelh

Great answer, It is very helpful to me .

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

Thanks again

Manesh

[swuehl]

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.