topic Re: Expression Help??? in QlikView

Expression Help???

amit_saini — Thu, 08 Oct 2015 05:45:21 GMT

Hi Folks ,

Please see the attachment and suggest why below expression is not working for ABC Target values:

if(GetFieldSelections(Major_Decfect_Grp_Cat)='Quality'

, sum({1<Plant_Name ={'$(=getfieldselections(Region1))'}, Month -={'-'}>}[QDR Target])

, if(getfieldselections(Major_Decfect_Grp_Cat)='Non-Quality'

,sum({1<Plant_Name ={'$(=getfieldselections(Region1))'}, Month-={'-'}>}[QDR Target NQ])

, sum({1<Plant_Name ={'$(=getfieldselections(Region1))'}, Month-={'-'}>}[QDR Target NQ] + [QDR Target])

)

Thanks,

Re: Expression Help???

jonathandienst — Thu, 08 Oct 2015 05:55:06 GMT

In what way is it not working?

Is Month-={'-'} intended to remove nulls?

The expression [QDR Target NQ] + [QDR Target] will return null if either value is null, and will be 0 in the sum. Use

Sum({....} RangeSum([QDR Target NQ] + [QDR Target]))

Or add two Sum() expressions together.

Please reduce the data in your sample:Preparing examples for Upload - Reduction and Data Scrambling

Re: Expression Help???

jonathandienst — Thu, 08 Oct 2015 05:56:44 GMT

Re: Expression Help???

jonathandienst — Thu, 08 Oct 2015 05:58:16 GMT

Please reduce the data in your sample:Preparing examples for Upload - Reduction and Data Scrambling

Re: Expression Help???

MK_QSL — Thu, 08 Oct 2015 06:05:45 GMT

I can't find Quality and Non-Quality in Decfect group? Am I missing something?

Re: Expression Help???

amit_saini — Thu, 08 Oct 2015 06:09:49 GMT

Hi Jonathan,

Already did this , size before was 180 MB and now I have reduced it to 18MB.

Yes Month-=('-') is to remove null , but if I'm keeping this simple Month= than also I'm not getting Target values.

I have tried rangesum also , it's not working.

Thanks,

Re: Expression Help???

amit_saini — Thu, 08 Oct 2015 06:11:39 GMT

Sorry Manish,

I did scrambling of data:

Ckocicw	Quality
Hyo-Mrczpyh	Non-Quality

Thanks,

Re: Expression Help???

MK_QSL — Thu, 08 Oct 2015 06:16:10 GMT

Because of Scrumbling,, Plant_Name will not match to GetFieldSelection(Region1)...

Re: Expression Help???

amit_saini — Thu, 08 Oct 2015 06:21:07 GMT

ahh... Yes you are right !

Re: Expression Help???

amit_saini — Thu, 15 Oct 2015 11:29:08 GMT

I tried below expression and it is working fine:

(if(GetFieldSelections(Major_Decfect_Grp_Cat)='Quality'

, sum({1<Region1={$(Variable1)}, Month-={'-'}>}[QDR Quality defects targets])

, if(getfieldselections(Major_Decfect_Grp_Cat)='Non-Quality'

,sum({1<Region1={$(Variable1)}, Month-={'-'}>}[QDR Non Quality defects targets])

, sum({1<Region1={$(Variable1)}, Month-={'-'}>}[QDR Non Quality defects targets] + [QDR Quality defects targets])

)

))/12

Thanks,