what is wrong with the expression

[rdsuperlike]

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.

[Clever_Anjos]

You don´t need to $() your variables

Are your receiving an error or you see no results?

[rdsuperlike]

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

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

[Clever_Anjos]

Would you mind sharing a sample of your app?

Preparing examples for Upload - Reduction and Data Scrambling

[Anonymous]

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

[swuehl]

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.

[#7]

I noticed that you have your else part missing.

If I break your expression down:

If

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

[jagan · Accepted solution]

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.