Count Set Analysis

[#1]

I've imported a SQL audit table. One of the columns is a DateTime format (TimeGenerated), I've also added a new column which does Date(TimeGenerated) as TimeGeneratedDate to remove the time part during the import.

As part of a Pivot table I'm trying to restrict the values/count based on the user supplied date range.

Initially I planned to use variables vDateFrom and vDateTo which the user will input values to and update the tables. But I've switched to hardcoded dates and still failing to get the correct results.

The expression I'm using in the chart properties is :

Count({$<

EntryType = {'Error'},

TimeGeneratedDate = {">=$(Date('23/10/2015'))"},

TimeGeneratedDate = {"<=$(Date('23/11/2015'))"}

>} EntryType)

If I remove the two TimeGeneratedDate lines, this expression will work great, it filters the result set to only counting the values with "Error" in EntryType. but now I try to control the data being counted by restricting it to only values in the user provided date range, but this has no affect on the result at all.

I've tried comparing the (TimeGenerated) which was the SQL DateTime format imported, and against (TimeGeneratedDate) which has removed the time part and left me with dates only. It both fails.

any help would be great please.

ps. 2nd day of using Qlik. sorry if its something so simple.

Thanks

Rob

[sunny_talwar]

Try this:

Count({$<EntryType = {'Error'}, TimeGeneratedDate = {"$(='>=' & Date(MakeDate(2015, 10, 23), 'YourDateFieldFormatHere') & '<=' & Date(MakeDate(2015, 11, 23), 'YourDateFieldFormatHere'))"}>} EntryType)

Where this -> ='>=' & Date(MakeDate(2015, 10, 23), 'YourDateFieldFormatHere') & '<=' & Date(MakeDate(2015, 11, 23), 'YourDateFieldFormatHere')

in a text box object should give you the range you want to be included in your expression and in the same format as TimeGeneratedDate.

HTH

Best,

Sunny

[rubenmarin]

Hi Rob, the $-Expansion needs a starter '=' to evaluate the expression:

Count({$<

EntryType = {'Error'},

TimeGeneratedDate = {">=$(=Date('23/10/2015'))"},

TimeGeneratedDate = {"<=$(=Date('23/11/2015'))"}

>} EntryType)

If you set this as an expression in a table without a label, the label will the the expanded expression and you can check how QV is interpreting you $-expanded expression.

In example, if you have a vMaxYear variable with the value "=Max(Year)" using only $(vMaxYear) works because the variable has the starting '='.

[#4]

Hi Sunny,

With the hardcoded dates that works really well, now if I was to look at upgrading that to a variable, I am breaking things again. I'd like to make it use a variable as the date range can be pulled in to multiple objects in the document.

Count({$<EntryType = {'Error'}, TimeGeneratedDate = {"$(='>=' & Date($(vDateFrom), 'DD/MM/YYYY') & '<=' & Date($(vDateTo), 'DD/MM/YYYY'))"}>} EntryType)

This is resulting in zero's in all results

[sunny_talwar]

How do you declare your variables?

vDateTo = ?

vDateFrom = ?

[rubenmarin · Accepted solution]

try simple-quoting the dates:

Count({$<EntryType = {'Error'}, TimeGeneratedDate = {"$(='>=' & Date('$(vDateFrom)', 'DD/MM/YYYY') & '<=' & Date('$(vDateTo)', 'DD/MM/YYYY'))"}>} EntryType)

[#7]

Brilliant, thanks so much both sunindia and RubenMarin‌, this has worked a treat.

I tried the quotes but think I put the quotes both inside the () rather than outside the $() like you have Ruben.

All fixed, many thanks both.