Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jenmclean
New Contributor II

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

Re: Only show invoices with no cost

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
New Contributor II

Re: Only show invoices with no cost

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

Not applicable

Re: Only show invoices with no cost

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.

MVP
MVP

Re: Only show invoices with no cost

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

Re: Only show invoices with no cost

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
New Contributor II

Re: Only show invoices with no cost

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

Community Browser