topic Last date data in QlikView

Last date data

Wed, 20 Apr 2011 11:14:12 GMT

hi all,

i'm having a problem that is maybe a bug or my idea might be wrong:

i ve a table:

Customer ID / PayementWeek / Amount

234 / 201101 / 1200

234 / 201102 / 3000

201101 as the first week of 2011

and my real table is with thousands registers starting in 2000 for more than 7000 customers...

and i need to show only the last line. (would be 201113 for this week... and the amount corresponding)

I have as dimensions: CustomerID and Amout

and as an expression: =aggr(Max(PayementDate),CustomerID)

but the Amount given is wrong and is taken like randomly in the table.

Any suggestion would be appreciated.

Thanks

Last date data

Miguel_Angel_Baeyens — Wed, 20 Apr 2011 11:31:35 GMT

Hello,

The following should work

Sum(If(Aggr(NODISTINCT Max(PayementWeek), [Customer ID]) = PayementWeek, Amount))

FirstSortedValue will work as well.

Hope that helps.

Last date data

Wed, 20 Apr 2011 12:48:46 GMT

Muchissimas gracias Miguel, funciona la ostia 😉

Last date data

Wed, 20 Apr 2011 13:32:32 GMT

I might have something a bit more sneaky to solve 😃

In the same table, imagine that the last Amount is equal to 0 and i wish to show the last transaction date and amount that is different from zero.

actually, you can have in this table a customer that have paid his last amount several weeks ago, you would have then several weeks with a 0 amount, but we want to know when is the last time he paid.

Is that possible as a dimesion? or should i create a new table?

Hope i'm clear,

Thx again

Re: Last date data

Miguel_Angel_Baeyens — Wed, 20 Apr 2011 13:41:26 GMT

Hello,

This post is about something very similar.

Considering this piece of script, simulating the case you specify:

Data:

LOAD * INLINE [

Customer ID, PayementWeek, Amount

234, 201101, 1200

234, 201102, 3000

234, 201103, 0

234, 201104, 0

234, 201105, 0];

The following expression should return the proper value (3000):

Sum(If(Aggr(NODISTINCT Max({< Amount = {'>0'} >} PayementWeek), [Customer ID]) = PayementWeek, Amount))

I'm not very happy with this, though, if your table is not huge it may do the trick.

Hope that helps

Last date data

Wed, 20 Apr 2011 13:56:01 GMT

Eres un maestro, thanx a lot, solved my issue perfectly.