Use or multiple "IF" statements in expression

[fgirardin]

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

[tunoi]

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]

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 · Accepted solution]

Try this

Edited Sunny's expression

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

[neelamsaroha157]

Here is sample qv.

[fgirardin]

Thank you Neelam,
It's working perfectly

Kind regards