Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
fgirardin
Creator
Creator

Use or multiple "IF" statements in expression

Hello,

I have a simple invoice list with different customers and I'm trying to use multiple IF statements in my expression

Here's my table

Date   Invoice N°   Customer Name    Item    Description   Remark   Amount

               1                    A                    AA          AAA          OK          100

               2                    B                    BB          BBB          NA          200

               3                    C                    CC          CCC         OK          150

               4                    D                    DD          DDD          OK          100

To get the sum of my invoices I use a simple expression:

SUM(Amount)


Total = 550

Now I would like to get the SUM of only specific invoices

What I did is use a basic IF Statement

SUM(IF(Remark='NA', 0, Amount))

Total = 350

This is very basic (and I'm sure there is a better way to do it) but it works for the purpose of my report

The problem appears when I try to use multiple "IF"

SUM(IF(Remark='NA' or Description='CCC', 0, Amount))

The result is incorrect as SOME of my amounts (only some specific customers, wich puzzles me) get multiplied by 2

Date   Invoice N°   Customer Name    Item    Description   Remark   Amount

               1                    A                    AA          AAA          OK          200  <<<<

               2                    B                    BB          BBB          NA          0

               3                    C                    CC          CCC         OK          0

               4                    D                    DD          DDD          OK          100

The data is the same for all my entries, I don't understand why some amounts change and others don't

I'm sure there is a better way to achieve what I want


Thank you for your input and help


FG

1 Solution

Accepted Solutions
neelamsaroha157
Specialist II
Specialist II

Try this

Edited Sunny's expression

Sum({<Remark -= {'NA'}>*<Description -= {'CCC'}>} Amount)

View solution in original post

7 Replies
tunoi
Creator
Creator

Hi,

most likely you have that problem because of the data model...

Description field most likely comes from a different table and in that table AAA have 2 appearances?

sunny_talwar

Do you see the same issue when you use set analysis?

Sum({<Remark -= {'NA'}>+<Description -= {'CCC'}>} Amount)

fgirardin
Creator
Creator
Author

Hello,

If I use only one statement, it works, but as soon as I use 2, nothing happen

Example:

Sum({<Remark -= {'NA'}>} Amount)


Gives me


Date  Invoice N°  Customer Name    Item    Description  Remark  Amount

              1                    A                    AA          AAA          OK      100

            2                    B                    BB          BBB          NA        0

              3                    C                    CC          CCC          OK    150

              4                    D                    DD          DDD        OK    100


But using this


Sum({<Remark -= {'NA'}>+<Description -= {'CCC'}>} Amount)


Shows all the amounts


I tried with several other fields (date, customer name), always the same


NOTE: I forgot to mention (sorry) that I use a pivot table.

sunny_talwar

Would you be able to share a sample where you are trying this?

neelamsaroha157
Specialist II
Specialist II

Try this

Edited Sunny's expression

Sum({<Remark -= {'NA'}>*<Description -= {'CCC'}>} Amount)

View solution in original post

neelamsaroha157
Specialist II
Specialist II

Here is sample qv.

fgirardin
Creator
Creator
Author

Thank you Neelam,
It's working perfectly

Kind regards