Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. 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
Contributor III
Contributor III

The 'Budget' has been typed in the expression exactly as is in the field but even if I use this expression it should exclude all the other categories but it brings back data for all the categories.

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

Highlighted
Master II
Master II

Try as below:

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

Highlighted
Contributor III
Contributor III

Still getting all the categories on the report.  I'm attaching the screenshotqv.jpg

Highlighted
Master II
Master II

For the expression "Amount", I still see you are using brackets instead of flower bracket. Are you using the below expression:

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

Also after changing to the above expression can you also uncheck the expression Amount CR and Amount_DR and let me know what you see.

Highlighted
Contributor III
Contributor III

I just copied and pasted the expression from your post. I'll try again and disable the amount cr and Dr expressions.

Highlighted
Contributor III
Contributor III

Still no joy.  'Budget' category lines still show.  Screenshots attachedQv2.jpgQv3jpg.jpgQv4jpg.jpg

Highlighted
Contributor III
Contributor III

I've managed to sort this...for it to work I have to either add the AmountCR and AmountDR to the Dimensions or change the definition of the Amount_CR and Amount_CR as

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


and


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


Thanks so much for your help with this.  Really appreciate it.


I need to add a condition on another report to bring back data for only those lines where the release date is null but if I use this expression I get no data at all

sum({<APHold.ReleaseDate={' '}>}APInv.InvoiceAmount)

Any ideas?  Thanks again

Highlighted

Try with the inverse: excluding all lines for which ReleaseDate is not null. You'll have to make sure that ReleaseDate is actually NULL sometimes, and not just space or empty string. Those are not the same thing.

Sum({<APHold.ReleaseDate -= {"*"}>} APInv.InvoiceAmount)

Highlighted
Contributor III
Contributor III

I tried this expression and I get no data


Sum({<APHold.ReleaseDate -= {"*"}>} APInv.InvoiceAmount)

Highlighted

Sorry, my fault. You can't specify a set by keeping just the NULL values in a field.

Do you have another field that has values for all rows (like a Customer or Transaction ID)? Then you could use that one to filter out all lines without ReleaseDate value. For example:

Sum({<APInv.OrderNo = {"=IsNull(APHold.ReleaseDate)"}>} APInv.InvoiceAmount)