set analysis counting nulls(Urgent)

[hathwarjaya]

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

[Peter_Cammaert]

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

[Peter_Cammaert]

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)