Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

amit_saini
Honored Contributor III

Expression Help???

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,

AS

1 Solution

Accepted Solutions
amit_saini
Honored Contributor III

Re: Expression Help???

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,

AS

9 Replies
MVP
MVP

Re: Expression Help???

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Expression Help???

t1.png

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Expression Help???

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: Expression Help???

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

amit_saini
Honored Contributor III

Re: Expression Help???

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,

AS

amit_saini
Honored Contributor III

Re: Expression Help???

Sorry Manish,

I did scrambling of data:

  

CkocicwQuality
Hyo-MrczpyhNon-Quality

Thanks,

AS

Re: Expression Help???

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

amit_saini
Honored Contributor III

Re: Expression Help???

ahh... Yes you are right !

amit_saini
Honored Contributor III

Re: Expression Help???

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,

AS

Community Browser