topic set analysis counting nulls(Urgent) in QlikView

set analysis counting nulls(Urgent)

hathwarjaya — Mon, 19 Mar 2018 07:23:25 GMT

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

Re: set analysis counting nulls(Urgent)

Peter_Cammaert — Mon, 19 Mar 2018 16:42:58 GMT

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

Re: set analysis counting nulls(Urgent)

Peter_Cammaert — Mon, 19 Mar 2018 16:54:34 GMT

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)