Sum expression with a couple of IF's

[#1]

Hey everyone,

i started using more elaborated expressions yesterday and im finding myself in trouble with syntax and stuff.

My expression is this at the moment:

if

(count(DISTRIBUTION_LINE_NUMBER) >=2, if(count(distinct(INVOICE_PAYMENT_ID) >= 2 ), sum(INVOICE_AMOUNT)*count(distinct INVOICE_PAYMENT_ID)/ count(INVOICE_NUM) ,sum(distinct INVOICE_AMOUNT)), INVOICE_AMOUNT)

Is working fine in most of the cases but i dont understand how to manage specific cases where invoice_payment_ID is more than 2 AND distribution_line_number is more than 2.

Any tip or possible solution will be appreciated.

Regards,

Juan Pedro

[#2]

if

(count(DISTRIBUTION_LINE_NUMBER) >=2, if(count(distinct(INVOICE_PAYMENT_ID) >= 2 ), sum(INVOICE_AMOUNT)*count(distinct INVOICE_PAYMENT_ID)/ count(INVOICE_NUM), if
(count(distinct(INVOICE_PAYMENT_ID)>= 2) , suM(INVOICE_AMOUNT)/ COUNT(DISTINCT INVOICE_PAYMENT_ID)* count(distinct INVOICE_ID), sum(INVOICE_AMOUNT))),INVOICE_AMOUNT)

this is my new code but cant really understand if is right or im still missing something.

[#3]

Juan you can try this

if(count(DISTRIBUTION_LINE_NUMBER) >=2 or count(distinct(INVOICE_PAYMENT_ID) >= 2 )

    ,sum(INVOICE_AMOUNT)*count(distinct INVOICE_PAYMENT_ID)/ count(INVOICE_NUM)

    ,sum(INVOICE_AMOUNT))

[#4]

hey ishimada thank you for your anserw.

I already tried a OR before reaching my previous expression. Problem is that some invoices have multiple lines and multiple payments and thats whats making the expression not correct when the amount of data is too big. My expression works perfectly on single selections. The problems comes when a group of invoices has different types of invoices. Something like:

1 invoice with 1 distribution line and 1 payment_id

1 invoice with 3 distribution line and 2 payment_id

I guess i will have to post some example's to make the situation more clear for you guys.

Thanks Again,

Juan Pedro

EDIT: Attached a file with an example

[Anonymous]

Hi juanpedro,

Put the 'if' inside the 'sum', it should work correctly:

sum(if

(count(DISTRIBUTION_LINE_NUMBER) >=2, if(count(distinct(INVOICE_PAYMENT_ID) >= 2 ), INVOICE_AMOUNT*count(distinct INVOICE_PAYMENT_ID)/ count(INVOICE_NUM), if
(count(distinct(INVOICE_PAYMENT_ID)>= 2) , INVOICE_AMOUNT/ COUNT(DISTINCT INVOICE_PAYMENT_ID)* count(distinct INVOICE_ID), INVOICE_AMOUNT)),INVOICE_AMOUNT)

Check the code but the solution to your problem is to put the 'if' condition inside the 'sum'

[#6]

Hey there bobby,

i was gonna try your possible solution but i cant really make the code work :S

if(count(DISTRIBUTION_LINE_NUMBER) >=2,
if(count(distinct(INVOICE_PAYMENT_ID) >= 2 ), (INVOICE_AMOUNT)*count(distinct INVOICE_PAYMENT_ID)/ count(INVOICE_NUM),
if(count(distinct(INVOICE_PAYMENT_ID)>= 2) , (INVOICE_AMOUNT)/ COUNT(DISTINCT INVOICE_PAYMENT_ID)* count(distinct INVOICE_ID),
(INVOICE_AMOUNT))),
INVOICE_AMOUNT

)

This is the logic that is more than fine and in fact the expression is ok for QlikView. But when i add the sum there is an error somewhere and i cant really find it out.

[#7]

Up.

Cant really figure out how to write it myself

[#8]

Hi,

In your code,

i think the condition

count(distinct(INVOICE_PAYMENT_ID) >= 2 ) your ">= 2" is inside your count() function

should be

count(distinct INVOICE_PAYMENT_ID) >=2

Regards,

Alex

[#9]

hey there, im still trying to work with the if - sum since i cant really make the sum - if work. The >=2 was in fact inside the code but fixing it didnt change much. The only cases i cant manage at the moment are the cases that present different kind of invoices (see the example attached) .

The code expression im using atm is this one. 

if

(count(DISTRIBUTION_LINE_NUMBER) >=2, if(count(distinct DISTRIBUTION_LINE_NUMBER) >= 2 , sum(INVOICE_AMOUNT)*count(distinct INVOICE_PAYMENT_ID)/ count(DISTRIBUTION_LINE_NUMBER),
if(count(distinct INVOICE_PAYMENT_ID)>= 2 , suM(INVOICE_AMOUNT)/ COUNT(DISTINCT INVOICE_PAYMENT_ID)* count(INVOICE_ID), sum(INVOICE_AMOUNT)))
,sum(INVOICE_AMOUNT

))

Problem is when a case like this one happens.(see .xls file)

Dont know if the sum - if can fix the problem since i cant write a corret syntax for it

Thank you everyone

Juan Pedro

[Anonymous]

Hi juanpedro,

Try this:

=Sum(
     if(count

(DISTRIBUTION_LINE_NUMBER) >=2,
          (if(count(distinct INVOICE_PAYMENT_ID) >= 2,
               INVOICE_AMOUNT*count(distinct INVOICE_PAYMENT_ID)/count(INVOICE_NUM),
               (if(count(distinct INVOICE_PAYMENT_ID) >= 2,
                    INVOICE_AMOUNT/COUNT(DISTINCT INVOICE_PAYMENT_ID)*count(distinct INVOICE_ID),
                    INVOICE_AMOUNT))))),
     INVOICE_AMOUNT)