Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

set analysis urgent

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

7 Replies
wallinpeter
Contributor III
Contributor III

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)


Not applicable
Author

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
MVP
MVP

Hi,

Please find attached file for solution.

Regards,

Jagan.

Not applicable
Author

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

mov
Champion III
Champion III

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

Not applicable
Author

There goes the qvw.

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

Not applicable
Author

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.