if condition

[#1]

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.

[sunny_talwar]

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)))

)

[sunny_talwar]

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))

[rubenmarin]

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

[#5]

Hi Sunny,

Thanks for the expression.

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

But look at this another example

[#6]

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

Please help me to fix this

[sunny_talwar]

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)

)