topic Set Analysis Count If? in QlikView

Set Analysis Count If?

Thu, 09 Feb 2012 18:00:18 GMT

I would like this equation translated into Set Analysis. This should be fairly straight forward but all the examples show X field is equal to 'X, X.' I am unable to find the correct syntax for an if statement with a comparison.

If today’s < greater than the assigned to date count the number of tickets regardless ofany other selections.

=count(if(num(vToday)<num(ASSIGNEDDATE), (TICKETID)))

Let me know if you have any other questions.

Thanks.

Set Analysis Count If?

pover — Thu, 09 Feb 2012 18:20:26 GMT

Try

count({$<ASSIGNEDDATE={">$(vToday)"}> TICKETID)

Karl

Set Analysis Count If?

Thu, 09 Feb 2012 18:35:16 GMT

This did not work but I think you are on the right track. Just a matter of getting the syntax correct.

Set Analysis Count If?

pover — Thu, 09 Feb 2012 18:50:37 GMT

I don't know what vToday is, so try,

count({$<ASSIGNEDDATE={">$(=today())"}> TICKETID)

Set Analysis Count If?

Thu, 09 Feb 2012 19:02:07 GMT

vToday was just a variable made for today. I tried today() still does not work.

Set Analysis Count If?

Anonymous — Thu, 09 Feb 2012 19:08:41 GMT

Unlike "if", set analysis is sensitive to the date format. To work, both ASSIGNEDDATE and today() (or vToday) must be in the same format.

Set Analysis Count If?

pover — Thu, 09 Feb 2012 19:09:55 GMT

Ok, it should work. If you need more help, it will be easier if you can upload a small, sample qvw of your data.

Karl

Set Analysis Count If?

Thu, 09 Feb 2012 19:22:52 GMT

Yes I attempted this as well.

I am attempting to show how many tickets were opened on todays date. So assigned date for each ticket. I converted =num(assigneddate) AS ASSIGNEDDATE_NUM. vToday is also defined in my load script as a num value. See both text boxes below to view values. TICKETID is just as the name applies.

Re: Set Analysis Count If?

Thu, 09 Feb 2012 19:36:06 GMT

I have now taken assigneddate in my load script this:

floor(num(ASSIGNEDDATE)) AS ASSIGNEDDATE_NUM,

This now converts all assigned to dates to be equal to todays date. Just playing the the syntax again in set analysis. Will post if I get it working.

Set Analysis Count If?

Anonymous — Thu, 09 Feb 2012 19:38:49 GMT

You need to use ASSIGNEDDATE_NUM in your set instead of ASSIGNEDDATE

Re: Set Analysis Count If?

Thu, 09 Feb 2012 19:42:26 GMT

I am:

Attempt 1:

=count({1<ASSIGNEDDATE_NUM={'vToday'}>} TICKETID)

Attempt 2:

=count({1<ASSIGNEDDATE_NUM={vToday}>} TICKETID)

This works if I do this: (but I would like it dynamic)

=count({1<ASSIGNEDDATE_NUM={'40948'}>} TICKETID)

Set Analysis Count If?

Thu, 09 Feb 2012 19:46:11 GMT

'$(vToday)'

Re: Set Analysis Count If?

Anonymous — Thu, 09 Feb 2012 19:48:21 GMT

No need for if. Or youcan use if witout set. You have to use the example from Karl, but with

ASSIGNEDDATE_NUM

count({$<ASSIGNEDDATE_NUM={">$(vToday"}> TICKETID)

Re: Set Analysis Count If?

Thu, 09 Feb 2012 19:48:54 GMT

Thank you Federico and Michael. Both solutions work. Beleow is what I ended up using.

=count({1<ASSIGNEDDATE_NUM={$(vToday)}>} TICKETID)