Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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)

neelamsaroha157
Specialist II
Specialist II

Here is sample qv.

fgirardin
Creator
Creator
Author

Thank you Neelam,
It's working perfectly

Kind regards