topic Re: if condition in QlikView

if condition

Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

i have attached a sample qvw.

The requirement is, i need to count the invoices of a customer who bought 1 x FLAG 101 or above & 2 x FLAG 104 & above together.

in the attached qvw, i have two pivot tables. 1. Customer Invoice Details, 2. Sales Details

'Customer Invoice Details' gives the invoice count per customer

'Sales Details' gives the detailed sales of a customer.

i have given the condition as per the requirement. but the problem is if a customer returned the purchased FLAGS as per requirement, then it falls under different invoice.

for example customer no. o1006901 details below

customer bought the FLAGS 1 X 101 & 2 X 104 IN Invoice No. 05848522 but he returned exactly the same in Invoice No. 09747585.

so in the first pivot table, invoice shouldnt be counted against this customer. the value for 'No. of Invoices' should be zero.

How can i do this. Please help.

Re: if condition

sunny_talwar — Tue, 19 Apr 2016 10:10:33 GMT

May be this:

IF(

(COUNT (IF(AGGR(

Sum({<FLAG={'101'}>} ORDQTY) >= 1

AND

Sum({<FLAG={'104'}>} ORDQTY) >= 2, IVNO, CUSTNO), IVNO))) > 0 and Sum(Aggr(Sum(ORDQTY), BRCODE, CUSTNO)) <> 0,

(COUNT (IF(AGGR(

Sum({<FLAG={'101'}>} ORDQTY) >= 1

AND

Sum({<FLAG={'104'}>} ORDQTY) >= 2, IVNO, CUSTNO), IVNO)))

)

Re: if condition

sunny_talwar — Tue, 19 Apr 2016 10:21:11 GMT

and to get the total right, you can add the Sum(Aggr()) on top of it like this:

Sum(Aggr(IF(

(COUNT (IF(AGGR(

Sum({<FLAG={'101'}>} ORDQTY) >= 1

AND

Sum({<FLAG={'104'}>} ORDQTY) >= 2, IVNO, CUSTNO), IVNO))) > 0 and Sum(Aggr(Sum(ORDQTY), BRCODE, CUSTNO)) <> 0,

(COUNT (IF(AGGR(

Sum({<FLAG={'101'}>} ORDQTY) >= 1

AND

Sum({<FLAG={'104'}>} ORDQTY) >= 2, IVNO, CUSTNO), IVNO)))), BRCODE, CUSTNO))

Re: if condition

rubenmarin — Tue, 19 Apr 2016 10:32:17 GMT

Hi, I think I detected a logic gap, in example, with this sample data:

Customer	Invoice	Quantity
1	1	5
1	2	1
1	3	4
1	4	-5

If you're counting invoices it can return 1 or 2, depending if the -5 from invoice 4 is substrated from Invoice '1' or from Invoices 2+3

Re: if condition

Tue, 19 Apr 2016 11:58:55 GMT

Hi Sunny,

Thanks for the expression.

It worked fine for the example i quoted in the post.

But look at this another example

Re: if condition

Tue, 19 Apr 2016 12:00:26 GMT

this customer returned one FLAG. this invoice shouldnt be counted. but this is counted.

Please help me to fix this

Re: if condition

sunny_talwar — Tue, 19 Apr 2016 13:55:07 GMT

Try this:

Sum(

Aggr(

If(

(Count(

If(

Aggr(

Sum({<FLAG={'101'}>} ORDQTY) >= 1

and

Sum({<FLAG={'104'}>} ORDQTY) >= 2,

CUSTNO),

IVNO)

)) > 0,

(Count(

If(

Aggr(

Sum({<FLAG={'101'}>} ORDQTY) >= 1

and

Sum({<FLAG={'104'}>} ORDQTY) >= 2,

IVNO, CUSTNO),

IVNO)

))),

BRCODE, CUSTNO)

)