topic Re: Sum and nested if in qlikview script in QlikView

Sum and nested if in qlikview script

venkatg6759 — Mon, 15 Aug 2016 14:21:21 GMT

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

Re: Sum and nested if in qlikview script

dmac1971 — Mon, 15 Aug 2016 14:35:04 GMT

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

Re: Sum and nested if in qlikview script

venkatg6759 — Mon, 15 Aug 2016 15:00:42 GMT

In Load Script

Re: Sum and nested if in qlikview script

Colin-Albert — Mon, 15 Aug 2016 15:05:17 GMT

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.

Re: Sum and nested if in qlikview script

venkatg6759 — Mon, 15 Aug 2016 15:09:13 GMT

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;

Re: Sum and nested if in qlikview script

Anonymous — Mon, 15 Aug 2016 15:14:33 GMT

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)))))

Re: Sum and nested if in qlikview script

Colin-Albert — Mon, 15 Aug 2016 15:19:39 GMT

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().

Re: Sum and nested if in qlikview script

venkatg6759 — Mon, 15 Aug 2016 15:22:05 GMT

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

Can you suggest aggr expression on this ..?