Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Champion III
Champion III

may be this

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

or

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

Highlighted
Master II
Master II

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

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

Highlighted
Contributor III
Contributor III

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?

Highlighted
Master II
Master II

You can try as below:

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

Highlighted
Contributor III
Contributor III

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.

Highlighted
Champion III
Champion III

may be this

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

Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

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)

Highlighted
Master II
Master II

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