topic sum with firstsortedvalue and/or aggr - Problem in QlikView

sum with firstsortedvalue and/or aggr - Problem

Thu, 02 Dec 2010 15:03:14 GMT

Dear All
i have a situation like these:

NETWORK_NAME TRX_DATE TRX_NUMBER TRX_CLASS VALUE BOOKING_NUMBER LAST_UPDATE_DATE
OPEN TRAVEL NETWORK STANDARD 12/08/2006 00:00:00 ORIT060215286 payment -8.457,16 3517061 12/08/2006 00:00:00
OPEN TRAVEL NETWORK STANDARD 01/17/2007 00:00:00 ORIT070005481 Invoice 8.457,16 3517061 01/17/2007 00:00:00
OPEN TRAVEL NETWORK STANDARD 10/22/2010 00:00:00 ORIT100243613 Invoice 2.649,48 8554923 10/22/2010 00:00:00
OPEN TRAVEL NETWORK STANDARD 10/22/2010 00:00:00 ORIT100243613 Invoice 2.649,48 8554923 10/25/2010 00:00:00

what i need to do is a sum of VALUE for NETWORK_NAME and BOOKING_NUMBER , but for the same TRX_NUMBER (see record 3 and 4) i must read only the record with max LAST_UPDATE_DATE.

I have tryed with firstsortedvalue with aggr function but don't work fine.

Any idea?

Thank's in advance
Riccardo

sum with firstsortedvalue and/or aggr - Problem

Oleg_Troyansky — Thu, 02 Dec 2010 15:56:06 GMT

Riccardo,

let's try to construct your formula...

The max value should look like this:

AGGR( MAX(VALUE), NETWORK_NAME, BOOKING_NUMBER, TRX_NUMBER).

this aggregation can be summarized:

SUM(AGGR( MAX(VALUE), NETWORK_NAME, BOOKING_NUMBER, TRX_NUMBER))

To get summaries by Network and Booking#, use them as dimensions in your chart.

cheers,

sum with firstsortedvalue and/or aggr - Problem

Thu, 02 Dec 2010 17:10:05 GMT

Hi Oleg

i'm not sure to understand your solution ... i'm trying to explain better my problem.

i'have 2 record for the same invoice , but with different "last_update_date". For these i must read the record with the max last_update_date.

I used this formula but it seem been wrong.

AGGR(ENT_CURR_AMOUNT_DUE_REMAINING,TRX_NUMBER,TRX_CLASS,BOOKING_NUMBER) and after ..

firstsortedvalue(AGGR(ENT_CURR_AMOUNT_DUE_REMAINING,TRX_NUMBER,TRX_CLASS,BOOKING_NUMBER),-LAST_UPDATE_DATE)

The error is that the system read the first record and not the second.. :-/. and don't sum correctly .. :-((

I attached my application .. if you wont to see "OPEN TRAVEL NETWORK " ex.

Thank'you so much.

Riccardo

sum with firstsortedvalue and/or aggr - Problem

Oleg_Troyansky — Thu, 02 Dec 2010 18:41:04 GMT

Riccardo,

OK, then replace my MAX with the firstsortedvalue. You can construct this expression from the middle, "inside out":

First, construct your FirstSortedValue:

FirstSortedValue(ENT_CURR_AMOUNT_DUE_REMAINING, LAST_UPDATE_DATE * -1)

Then, enclose it in the AGGR:

AGGR(

FirstSortedValue(ENT_CURR_AMOUNT_DUE_REMAINING, LAST_UPDATE_DATE * -1)

, TRX_NUMBER,TRX_CLASS,BOOKING_NUMBER)

And then, enclose AGGR insude SUM:

SUM( AGGR(...) )

sum with firstsortedvalue and/or aggr - Problem

Thu, 02 Dec 2010 18:42:22 GMT

Try something like:

aggr(

if(Last Update Date=max(total <Invoice Number> Last Update Date),

Invoice Amount

)

,Invoice Number, Invoice Update Date

)

I didn't put your actual field names in there but hopefully you get the point. That's only going to work if you have like invoice number or customer number or something as a dimension. Otherwise, put sum before the aggr and end the sum at the bottom.

sum with firstsortedvalue and/or aggr - Problem

Thu, 02 Dec 2010 19:29:27 GMT

Dear all

i have tryed both solution.

just for information the solution with firstsortedvalued have sort problem (read the min last_update_date and not the max .. and i don't understand why.

the second solution work fine.

1)sum(aggr(FirstSortedValue(ENT_CURR_AMOUNT_DUE_REMAINING, LAST_UPDATE_DATE *-1),TRX_NUMBER,TRX_CLASS,BOOKING_NUMBER))

2) aggr(if(LAST_UPDATE_DATE=max(total <TRX_NUMBER> LAST_UPDATE_DATE),ENT_CURR_AMOUNT_DUE_REMAINING ),TRX_NUMBER, LAST_UPDATE_DATE)

Thank's again .

Regards

Riccardo