Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

sum of last value in aggregated dimension

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