Hello:
I am new to QLikView and I have a CSV document that I am loading into QV but I am running into difficulties when trying to get a specific aggregation.
the table form is like this:
TIMESTAMP | YEAR | MONTH | DAY | HOUR | 5_MIN_INT | ORIGIN | ATTRIBUTE | DATA
2013-12-04 00:02:35 | 2013 | 12 | 04 | 0 | 5 | ONE | SUCCESS | 57
2013-12-04 00:02:35 | 2013 | 12 | 04 | 0 | 5 | TWO | SUCCESS | 68
2013-12-04 00:02:35 | 2013 | 12 | 04 | 0 | 5 | ONE | FAIL | 23
2013-12-04 00:02:35 | 2013 | 12 | 04 | 0 | 5 | TWO | FAIL | 12
2013-12-04 00:03:55 | 2013 | 12 | 04 | 0 | 5 | ONE | SUCCESS | 69
2013-12-04 00:03:55 | 2013 | 12 | 04 | 0 | 5 | TWO | SUCCESS | 79
2013-12-04 00:03:55 | 2013 | 12 | 04 | 0 | 5 | ONE | FAIL | 34
2013-12-04 00:03:55 | 2013 | 12 | 04 | 0 | 5 | TWO | FAIL | 24
What I am looking for is an expression to get the sum of the last TIMESTAMP for each 5_MIN_INT:
ie the sum of all rows with ATTRIBUTE=FAIL and TIMESTAMP=2013-12-04 00:03:55 which is the Timestamp(Max(TIMESTAMP))
5_MIN_INT | FAILED_TRANS
5 | 58
I can get the whole sum with: sum({<ATTRIBUTE={FAIL}>}DATA) but then it also sums the other TIMESTAMP
So I tried using aggr(sum({<ATTRIBUTE={FAIL}>}DATA),TIMESTAMP) wich moves me closer to my desired value but since TIMESTAMP is not a dimension then it fails when I only use 5_MIN_INT so I tried:
FirstSortedValue(aggr(sum({<ATTRIBUTE={FAIL}>}DATA),TIMESTAMP),-TIMESTAMP) but I just can't get it.
BTW 5_MIN_INT is really a drill-down dimension going from YEAR->MONTH->DAY->HOUR->5_MIN_INT
