set analysis urgent

[#1]

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

I have read at least twice the whole help file for set analysis and I think I should get the correct answer but I have spent two days trying without success.

I have tried the following expressions (and other ones like these) but I Just can't get the desired result, these are the ones that actually make some result that seems to point in the right direction.

=sum({$<ATRIBUTO={FAIL},TIMESTAMP-=$(Min(TIMESTAMP))>}DATA)

=Sum({$<ATRIBUTO={FAIL},TIMESTAMP+={$(=FirstSortedValue(TIMESTAMP,-5_MIN_INT))}>}DATA)

=sum({<ATRIBUTO={FAIL},TIMESTAMP={P(TIMESTAMP=$(Max(TIMESTAMP))}>}DATA)

=sum({<ATRIBUTO={FAIL},TIMESTAMP={"=Max(Aggr(Max(TIMESTAMP),5_MIN_INT))"}>}DATA)

The last one actually DOES bring me the correct result but for the WRONG timestamp, ie not the last timestamp but the first one.

BTW 5_MIN_INT is really a drill-down dimension going from YEAR->MONTH->DAY->HOUR->5_MIN_INT

[wallinpeter]

Are you sure your timestamp is really a timestamp and not a text string.

You can tell by trying to format as a number in a tablebox object.

If timestamp is string. Try this....

sum({<ATTRIBUTE={'FAIL'}, TIMESTAMP={$(=maxstring(TIMESTAMP))}>}DATA)

If timestamp is number. Try this...

sum({<ATTRIBUTE={'FAIL'}, TIMESTAMP={$(=max(TIMESTAMP))}>}DATA)

[#3]

I am sure it is a numeric timestamp, I checked with a tablebox, I just tried putting another expression with:

Max(TIMESTAMP) and it returns me a number which corresponds to the timestamp.

Year(Max(TIMESTAMP)) does return me 2013.

I tried both suggestions... but none worked, in both cases I get NULL values (not zeroes).

[jagan]

Hi,

Please find attached file for solution.

Regards,

Jagan.

[#5]

I think you skipped attaching the file

I would attach my qvw but it is in Personal Edition and can only be opened on my computer

[Anonymous]

David, a qvw created in Personal Edition can be opened by any non-Personal.  Not the other way around.

[#7]

There goes the qvw.

The field names are in spanish but it is very straight forward.

[#8]

Thanks Jagan Mohan but it stops working with bigger data sets, it gives me just one value for the highest value in the actual set, not one value for each 5_MIN_RANGE.

You can see the qvw I uploaded in this discussion.