Expression help

[#1]

Hi All,

I am working on creating a Help Desk metric and I have the code ready in SQL. But I am unable to match it up in Qlikview. So, I need help on correcting the QV Expression, which is giving incorrect result.

My SQL Code is the following:

SELECT COUNT([Case ID])

  FROM [Table1]

  where [Group] ='Help Desk'

  AND MONTH(SnapDate) = 3

  AND YEAR(SnapDate) = 2012

  AND ((cast(SnapDate AS DateTime) - cast(isnull([Create Date],'') AS DateTime) >0))

  AND (((cast(SnapDate AS DateTime) - cast(isnull([Resolved Date/Time],'') AS DateTime) <1))

  OR isnull([Resolved Date/Time],'')='')

The code above counts all tickets that are open for the Help Desk group on all SnapDate for the month of March 2012. The data in the table in question is snapshot with a daily frequency. The Create Date could be any day on or prior to the SnapDate. The Resolved Date/Time establishes that the ticket being counted was open at some point during the SnapDate in question. I get 8384 tickets for the month of March.

Examples of SnapDate, Create date and Resolve Date/Time respectively are

2012-03-08 00:00:00      03/07/2012 09:42:11     03/08/2012 18:24:19 or Space (' ')

My QV expression, which I need help with is given below

count({<[Group] = {'Help Desk'} >}

if((((DayStart(SnapDate) - CreateDateTime))*24>0 AND ((DayStart(AsOfDate) - ResolvedDateTime )*24<24 OR ResolvedDateTime = '' ))

AND Month(SnapDate) = 3

AND Year(SnapDate) = 2012,[Case ID]))

I tried the expression above with the time part removed for all the dates but it gives the same number of records which is 5262

Examples of SnapDate, CreateDateTime and Resolve Date/Time respectively are

03/08/2012    03/07/2012 9:42:11 AM    03/08/2012 6:24:19 PM

I would highly appreciate if someone could point out what am I doing wrong.

Thanks

AM

[#2]

Are you able to make a connection to sql database via Qlikview?

If so you can still use your sql script:

Load *;

sql SELECT

  COUNT([Case ID])

  FROM [Table1]

  where [Group] ='Help Desk'

  AND MONTH(SnapDate) = 3

  AND YEAR(SnapDate) = 2012

  AND ((cast(SnapDate AS DateTime) - cast(isnull([Create Date],'') AS DateTime) >0))

  AND (((cast(SnapDate AS DateTime) - cast(isnull([Resolved Date/Time],'') AS DateTime) <1))

  OR isnull([Resolved Date/Time],'')='')

[#3]

Thanks for the suggestion Frank however there are quite a few other things happening in the load script to bring the expression as is. I will keep it in mind though. I'm however now thinking that I might have to modify my SQL query and use something like datediff to calculate the difference in days between the dates and then try to match that up in QV. Still working on it.

Thanks,

AM