6 Replies Latest reply: Nov 24, 2015 11:09 AM by Rob Williams RSS

    Count Set Analysis

    Rob Williams

      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

        • Re: Count Set Analysis
          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

          • Re: Count Set Analysis
            Ruben Marin

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