Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

set analysis counting nulls(Urgent)

hello ,

I have this expression where i am counting the tickets for last 13 months where the amount is null or 0.

My expression works but i need to write it in set analysis.

Please can somebody help me with this

count(if(Monthname(sales.Date)>=monthname(AddMonths(Date(Today()), -13)),if(isnull(amount)<>0 or amount=0,tickets)))

Thanks & Regards,

Jaya

2 Replies
Highlighted

Do these "tickets" have a field with a unique identifier?

Highlighted

How about this (only works if every ticket.ID occurs only once in your facts table)?

=Count({<[sales.Date] = {'>=$(=MonthStart(Today(), -13))'},

         [ticket.ID]  = {"=RangeSum(amount) = 0"}>} tickets)

You may have to tune the expression a little bit to match your fields and data. Also keep in mind that if the string representation of sales.Date doesn't match the default format, you may need an additional Date() call with a matching format string, like:

...[sales.Date] = {'>=$(=Date(MonthStart(Today(), -13), ''D/MM/YYYY''))'}...


(those quotes around the format string are two single quotes each, not double quotes)