Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
imtiazgirach
New 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

Tags (2)
23 Replies
Chanty4u
Esteemed Contributor III

Re: Add condition

may be this

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

or

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

trdandamudi
Honored Contributor II

Re: Add condition

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

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

imtiazgirach
New Contributor III

Re: Add condition

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

Re: Add condition

You can try as below:

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

imtiazgirach
New Contributor III

Re: Add condition

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
Esteemed Contributor III

Re: Add condition

may be this

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

imtiazgirach
New Contributor III

Re: Add condition

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

Re: Add condition

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

Re: Add condition

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