sum() script with several where conditions

Thu, 19 Jun 2014 12:09:53 GMT

Hi Guys,

I still don't get that problem solved, to get several where conditions in one expression sum()

ODBC CONNECT TO NAV;
Load Amount,
    "Global Dimension 1 Code" as X,
    "Global Dimension 2 Code" as Y,
    "G_L Account No_" as Z,
    "Posting Date",
    Month("Posting Date") as Month,
    Year("Posting Date") as Year,
    sum(if("Global Dimension 1 Code" = 'T83400' or "Global
Dimension 1 Code"='T83500' or "G_L Account No_">=400000 and "G_L
Account No_"<=479999 , Amount)) as ALL.AMOUNT Group By Amount, "Global Dimension 1 Code", "Global
Dimension 2 Code","G_L Account No_", "Posting
Date"
    ;

SQL SELECT Amount,
    "Global Dimension 1 Code",
    "Global Dimension 2 Code",
    "Posting Date",
    "G_L Account No_"
FROM "DB ".dbo."DB7";

_____________

I tried with

- sum(if(...)Amount) as ALL.AMOUNT;

- sum(if((...),Amount) as ALL.AMOUNT;

- sum(Amount) as ALL.AMOUNT WHERE (...) Group By ...;

___

Load "Global Dimension 1 Code",
"Global Dimension 2 Code",
"G_L Account No_", "Posting Date",

sum(Amount) as Amount.ALL WHERE("G_L Account No_">=400000 and "G_L Account No_"<=479999 or "Global Dimension 1 Code"= 'T83400' or "Global Dimension 1 Code" = 'T83500') Group By Amount, "Global Dimension 1 Code","Global Dimension 2 Code",
"G_L Account No_", "Posting Date" ;

______

My Intention is to filter a sum(Amount) with more then 1 condition like:

Sum(Amount) where ("X" = 'T83400', "X"='T83500' , "Z"<479999, Z>400000) ...

When I have to use OR / AND ? How can I give this conditions to one sum()?

Re: sum() script with several where conditions

Gysbert_Wassenaar — Thu, 19 Jun 2014 12:49:34 GMT

When you use both OR and AND you need to use parentheses to make sure you get the right result. A or B and C can mean different things:

(A or B) and C has two solutions:
- A and C
- B and C
A or (B and C) has two different solutions:
- A
- B and C

topic sum() script with several where conditions in QlikView

sum() script with several where conditions

Re: sum() script with several where conditions