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: 
imtiazgirach
Contributor III
Contributor III

Add condition

Hi...I'd like to add a condition to a straight table report to exclude data for certain types of journal category but I am not sure how to do this in the expression.

The exclusion condition I need is

(GLSubLedg.JournalCategory) <> 'Budget' AND (GLSubLedg.JournalCategory) <> 'Payments'

I would really appreciate some assistance with this.  Thanks

23 Replies
Chanty4u
MVP
MVP

may be this

sum({<GLSubLedg.JournalCategory-={'Budget',' Payments'}>}Sales)

or

sum({<GLSubLedg.JournalCategory/={'Budget',' Payments'}>}Sales)

trdandamudi
Master II
Master II

Say if you have a field amount you can use the below expression:

Sum({<GLSubLedg.JournalCategory-={'Budget','Payments'}>} Amount)

imtiazgirach
Contributor III
Contributor III
Author

Thanks. I don't have an amount field on its own but I do have amount cr and amount dr fields so I can add a calculated fields amount cr - amount dr to get an amount field of that works as in some fields there might be amount cr and in others just amount dr.  Not sure if that would make a difference in the expression?

trdandamudi
Master II
Master II

You can try as below:

Sum({<GLSubLedg.JournalCategory-={'Budget','Payments'}>} amount cr - amount dr)

imtiazgirach
Contributor III
Contributor III
Author

Thanks. I will try this and let you know.

To help me understand for future requirements if I needed to change the condition to pick up data for where the journal category is say Sales so rather than exclude, the condition would be journal category = 'Sales' how would I need to change the condition.

Chanty4u
MVP
MVP

may be this

sum({<GLSubLedg.JournalCategory={'Sales'}>} amount cr - amount dr)

imtiazgirach
Contributor III
Contributor III
Author

I am getting errors in expression in all the above.  I am using Qlikview version 11 not sure if that makes a difference.  Thanks

imtiazgirach
Contributor III
Contributor III
Author

I slightly amended the expression and now the expression is ok but the report is still showing lines with the 'Budget' journal category.  I don't want these lines on the report

This is the amended expression:

Sum({<GLSubLedg.JournalCategory-={'Budget'}>} GLSubLedg.AmountCR-GLSubLedg.AmountDR)

Even with this expression the budget category lines are showing on the report:

sum({<GLSubLedg.JournalCategory=['Permanent','Reversing']>}GLSubLedg.AmountCR-GLSubLedg.AmountDR)

trdandamudi
Master II
Master II

It should work. Is the 'Budget' field case sensitive ?