Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jenmclean
Contributor III
Contributor III

Only show invoices with no cost

I have a chart that I am working on and am having an issue on how to write the expression so that it only shows invoices and a specific detail type (INV|DetTypeID) with $0 as the INV|UnitLastCost. My expression is a follows:

Sum({$<INV|VoidFlag={N},INV|DetTypeID={1,2,3,12,13,14}, Only(INV|UnitLastCost=0)>}INV|UnitLastCost)

The returned value should be 0. If I run it as Only(INV|UnitLastCost=0), then it returns a -1...

How can I write this correctly.

Thanks in advance!

6 Replies
Not applicable

I don't believe you need the only function.  Please try this

Sum({$<INV|VoidFlag={'N'},INV|DetTypeID={1,2,3,12,13,14}, [INV|UnitLastCost]={0}>}INV|UnitLastCost)

jenmclean
Contributor III
Contributor III
Author

No it did not work, this does not return any data then.

Not applicable

Instead of using sum, maybe you should try count. In this case instead of INV|UnitLastCost use the invoice id.

Count({$<INV|VoidFlag={'N'},INV|DetTypeID={1,2,3,12,13,14}, [INV|UnitLastCost]={0}>}InvoiceID).  Hope this helps.

maxgro
MVP
MVP

do you want to sum INV|UnitLastCost when INV|UnitLastCost=0?

Sum({$<INV|VoidFlag={N},INV|DetTypeID={1,2,3,12,13,14}, INV|UnitLastCost={0}>} INV|UnitLastCost)

Not applicable

I wanted to add that you should have the invoiceid as a dimension in the chart.

This way you are counting invoices with the attributes you're looking for. Is this what you want?

jenmclean
Contributor III
Contributor III
Author

This is closer to what I need. Will let you know...