Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
if
(count(DISTRIBUTION_LINE_NUMBER) >=2, if(count(distinct(INVOICE_PAYMENT_ID) >= 2 ), sum(INVOICE_AMOUNT)*count(distinct INVOICE_PAYMENT_ID)/ count(INVOICE_NUM), ifthis is my new code but cant really understand if is right or im still missing something.
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))
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
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), ifCheck the code but the solution to your problem is to put the 'if' condition inside the 'sum'
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.
Up.
Cant really figure out how to write it myself
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
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
Hi juanpedro,
Try this:
=Sum(
if(count