topic Re: Use or multiple "IF" statements in expression in QlikView

Use or multiple "IF" statements in expression

fgirardin — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Use or multiple "IF" statements in expression

tunoi — Fri, 31 Mar 2017 07:37:57 GMT

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?

Re: Use or multiple "IF" statements in expression

sunny_talwar — Fri, 31 Mar 2017 08:14:23 GMT

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

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

Re: Use or multiple "IF" statements in expression

fgirardin — Fri, 31 Mar 2017 12:51:58 GMT

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.

Re: Use or multiple "IF" statements in expression

sunny_talwar — Fri, 31 Mar 2017 12:54:55 GMT

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

Re: Use or multiple "IF" statements in expression

neelamsaroha157 — Fri, 31 Mar 2017 14:29:34 GMT

Try this

Edited Sunny's expression

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

Re: Use or multiple "IF" statements in expression

neelamsaroha157 — Fri, 31 Mar 2017 14:37:09 GMT

Here is sample qv.

Re: Use or multiple "IF" statements in expression

fgirardin — Mon, 03 Apr 2017 06:46:08 GMT

Thank you Neelam,
It's working perfectly

Kind regards