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]

Hi,

Well I don't really know what your goal is or what you want to do. All I know from reading your post is that you're having trouble/confused with the syntax of your current if statement and that you can't handle multiple entries of your invoice_payment_ID and distribution_line_number.

Can you tell me what you want to do if you have multiple invoice_payment_ID and distribution_line_number?

I checked your Excel Sample with 2 (1 and 2) distribution_line_number of (135759) INVOICE_NUM.

what do you want to happen in your INV AMOUNT Field?

Please see attached image

Regards,

Alex

[#3]

hey bobby,

copy pasting your code the you gave me i cant execute it since there are some errors in it as you can see in the copy paste i did on qlikview:

=

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)

I tried to fix the errors obtaining this result that is not working aswell . Im getting really frustated on here i consulted the reference and all the material i was able to find on the internet but i cant figure out how to write a correct sum - if

Other version:

=

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

))

Any further help will be apreciated.

Thanks for your time

Juan Pedro

[#4]

Hey alex didnt saw your post before.

The expected result is exactly what i get. What i dont want is the TOTAL to be falsed by this kind of Invoices. The right total for those 4 invoices in the example is 374.86 and not 398.664 as the expression shows at the moment.

My goal is to create a expression that can handle all the tipe of invoices i have to deal with and that dosent False the total cost of the invoices for the project.

[#5]

Hi,

So your current report is right? the only thing you want to fix is the total?

if so, you might want to use dimensionality function.

I had the same requirement last year and that's what I used.

Unfortunately I'm already out of the office so I cant send you some sample.

try using dimensionality. if you cant produce what you need i'll help you tomorrow just bump your thread again so I can find it.

Regards,

Alex

[#6 · Accepted solution]

Thank everyone for the help i solved my problem using the aggr() and is working perfectly now.

The Dimensionality thing made me think about it so thank you for that

Have a nice day everyone.

Juan Pedro

[#7]

Hi,

yes.... what I did with my project was use dimensionality and aggr to fix my total ^^

its good you solved it

Regards,

Alex