Search code examples
qlikviewqliksenseqlik-expressionset-analysis

QlikSense Set Analysis - Sum Less than HH:MM Format

I am trying to calculate a sum where certain Time criteria are met. My date column STime is stored in a HH:MM format (13:15, 17:25, etc.).

I have a variable, vCurrentTime, in the same format, calculated as:

TIME(NOW(), 'HH:MM')

Here is what my data looks like:

Person     STime   Points
Alex       15:00     10
Brandon    11:50     8
Carlos     16:04     12
Denise     13:25     4
Elise      16:09     30
Felicia    17:50     31
George     18:00     35

I'd like to sum the Points column in all instances where STime is less than vCurrentTime, or at the time of posting this question, about 14:45.

Only 2 rows meet that criteria (Brandon and Denise), so my desired output would be 12 (4+8).

The set analysis I'm using below has been returning 0:

SUM({$<STime = {"<=$(=$(vCurrentTime))"}>}Points)
Solution

  • As you can see from the screenshot below there is difference in the time based on the format:

    Time

    • TIME(NOW(), 'hh:mm') - returns the correct system time (20:04)
    • TIME(NOW(), 'HH:MM') - returns the correct hour but the minutes are wrong.

    The difference between mm and MM is that mm is describing the minutes of the hour and MM is the month of the year (thats why the "minutes" are 12 aka December)

    Once this is in place the expression seems to work ok in my case

    • left table - Raw data view (ive added one extra record for `Person = TEST)
    • middle table - Person as dimension and copy/paste the expression that is in the question. As you can see TEST record is not present because its STime is 21:00 and the current time is 20:04
    • right table - sum(Points) without any set analysis
    • bottom - the corrected variable view

    Data