Last date data

[#1]

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

[Miguel_Angel_Baeyens]

Hello,

The following should work

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

FirstSortedValue will work as well.

Hope that helps.

[#3]

Muchissimas gracias Miguel, funciona la ostia 😉

[#4]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[#6]

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