topic Re: what is wrong with the expression in QlikView

what is wrong with the expression

rdsuperlike — Thu, 14 May 2015 19:30:01 GMT

I am not able to get the below expression working.

Whats wrong with the syntax.

logic is If stage = wafer and Type = '%sdo%' and due dt + LT + bunch of variables match the DI DAy date then I need to do sum(qty)

SUM(IF((Upper(STAGE)='WAFER' AND TYPE like '*SDO*' AND (DUE_DT + LEADTIME + ($(x)+$(y)+$(z)+$(a)+$(b)+$(c)+$(d)) = DI_DAY_DATE)),QTY))

Any help is appreciated.

Re: what is wrong with the expression

Clever_Anjos — Thu, 14 May 2015 19:35:22 GMT

You don´t need to $() your variables

Are your receiving an error or you see no results?

Re: what is wrong with the expression

rdsuperlike — Thu, 14 May 2015 19:44:20 GMT

It is all showing me 0, while it shouldnt show 0.

Yeah.I tried removing $() too. But same result

Re: what is wrong with the expression

Clever_Anjos — Thu, 14 May 2015 19:47:09 GMT

Would you mind sharing a sample of your app?

Preparing examples for Upload - Reduction and Data Scrambling

Re: what is wrong with the expression

Anonymous — Thu, 14 May 2015 19:51:34 GMT

Can you confirm if all the components here

(DUE_DT + LEADTIME + ($(x)+$(y)+$(z)+$(a)+$(b)+$(c)+$(d))

are whole dates, not a single timestamp?

Edit:

And can you replace

TYPE like '*SDO*'

with

wildmatch(TYPE, '*SDO*')

Re: what is wrong with the expression

swuehl — Thu, 14 May 2015 22:28:32 GMT

I would first check that the condition without the last part (date comparison) is working.

Then check that the sum of the variables return a value (please note that every summand needs to evaluate to a number, if any summand evaluates to NULL, the sum will be NULL. A safer way to add these summands would be rangesum() function). Latter is also valid for the addition of the two date fields.

Your date fields must have a numeric representation.

All in all, if you're issue is still present after re-checking, I agree with Clever Anjos that it would make things much easier if you upload a small sample that demonstrates your issue.

Re: what is wrong with the expression

Fri, 15 May 2015 01:52:54 GMT

I noticed that you have your else part missing.

If I break your expression down:

STAGE='WAFER' AND

TYPE="*SODO*" (Use double quotes to use search expressions) AND

(DUE_DT + LEADTIME + ($(x)+$(y)+$(z)+$(a)+$(b)+$(c)+$(d))) = DI_DAY_DATE

THEN SUM(QTY)

ELSE ??? SO your else is missing. Please replace the else part(in BOLD) with the proper value or field.

SUM(IF(Upper(STAGE)='WAFER' AND TYPE= "*SDO*" AND (DUE_DT + LEADTIME + ($(x)+$(y)+$(z)+$(a)+$(b)+$(c)+$(d))) = DI_DAY_DATE,QTY,ELSE PART HERE))

hope this helps

Re: what is wrong with the expression

jagan — Fri, 15 May 2015 02:27:41 GMT

Hi,

Can you attach some sample data, so that it would be easier to find the issue. Is x, y, z, a , b, c d are dimensions or variables?

Try

SUM(IF(Upper(STAGE)='WAFER' AND TYPE like '*SDO*' AND TimeStamp(Rangesum(DUE_DT, LEADTIME, x, y,z, a, b, c, d)) = TimeStamp(DI_DAY_DATE,QTY))

Hope this helps you.

Regards,

Jagan.