Sum and nested if in qlikview script

[venkatg6759]

Hello Everyone,

I am trying to use sum and nested If in qlikview script but it's not working . Can anyone help me on this ?

sum(IF([tech_YTD Plan HC]=0 AND [tech_YTD Actual HC] <> 0,'INF',

IF([tech_YTD Plan HC]=0,''

,(100*(sum([tech_YTD Actual HC] - [tech_YTD Plan HC])/(FABS(sum([tech_YTD Plan HC]))+.00001)))))

Thanks,

Venkata Gogineni

[dmac1971]

Hi are you trying to do this in the load script or in an expression?

[venkatg6759]

In Load Script

[Colin-Albert]

Can you post the full load script for this table, as you will need to have a "group by" clause if you are using sum in the load script.

[venkatg6759]

Hi Colin,

I am doing group by class already but it's returning invalid expression due to nested if

Load Distinct
Key,

sum(IF([tech_YTD Plan HC]=0 AND [tech_YTD Actual HC] <> 0,'INF',

IF([tech_YTD Plan HC]=0,''

,(100*(sum([tech_YTD Actual HC] - [tech_YTD Plan HC])/(FABS(sum([tech_YTD Plan HC]))+.00001)))))

Resident Table Group by Key;

[Anonymous]

Hi

Am I seeing extra bracket after?? ........................+0.00001)))))

sum(IF([tech_YTD Plan HC]=0 AND [tech_YTD Actual HC] <> 0,'INF',

IF([tech_YTD Plan HC]=0, '    '

,(100*(sum([tech_YTD Actual HC] - [tech_YTD Plan HC])/(FABS(sum([tech_YTD Plan HC]))+.00001)))))

[Colin-Albert]

You need to either include the individual fields that are in your if statement [tech_YTD Plan HC] [tech_YTD Actual HC] in the group by statement or add a suitable aggregation expression around them such as min(), max(), minstring(), maxstring().

[venkatg6759]

i added them in group by clause but I am getting nested aggregation not allowed.

Can you suggest aggr expression on this ..?