Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
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
rubenmarin

try simple-quoting the dates:

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

View solution in original post

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

Not applicable
Author

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

try simple-quoting the dates:

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

Not applicable
Author

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.