Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Count Set Analysis

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

1 Solution

Accepted Solutions

Re: Count Set Analysis

try simple-quoting the dates:

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

6 Replies
MVP
MVP

Re: Count Set Analysis

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

Re: Count Set Analysis

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 '='.

Not applicable

Re: Count Set Analysis

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

MVP
MVP

Re: Count Set Analysis

How do you declare your variables?

vDateTo = ?

vDateFrom = ?

Re: Count Set Analysis

try simple-quoting the dates:

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

Not applicable

Re: Count Set Analysis

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.