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

    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

    Data