Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hello,
The following should work
Sum(If(Aggr(NODISTINCT Max(PayementWeek), [Customer ID]) = PayementWeek, Amount))
FirstSortedValue will work as well.
Hope that helps.
Muchissimas gracias Miguel, funciona la ostia 😉
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
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
Eres un maestro, thanx a lot, solved my issue perfectly.