Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to count the amount withdrawn by each user in a range of dates and came up with this:
Sum(distinct if(Date([Withdrawals.PaymentDate]) <= Date(Aggr(nodistinct SomeDays +
min([Deposits.PaymentDate]),[UserId])), [Withdrawals.BaseAmount], 0))
I do realise that the 'distinct' returns errors (it does not account for withdrawals made of the same amount within the dates).
However, without it the final output is waaay too big.
Managed to come by the right solution:
Sum(if(Date([Withdrawals.PaymentDate]) <= Aggr(nodistinct Aggr(nodistinct min([Deposits.PaymentDate]),UserId),[Withdrawals.UserId]) + SomeDays, [Withdrawals.BaseAmount]))
Hi,
it is better to share sample app or sample data
I have:
Withdrawal Date Amount UserId
1/1/18 10 1
1/1/18 10 1
2/1/18 20 1
1/1/18 30 2
2/1/18 10 3
Extract The amount withdrawn up to:
Date(Aggr(nodistinct min([Deposits.PaymentDate]) + SomeDays, UserId))
For the example above let's say it equals to: 1/1/18.
Expected Result:
UserId Amount
1 20
2 30
3 0
EDITED
try this:
sum({<[Withdrawal Date]={"<=1/1/18"}>}Amount)
To my actual dates and variables, that would translate into:
sum({<[Withdrawals.PaymentDate] <= {Date(Aggr(nodistinct SomeDays +
min([Deposits.PaymentDate]),[UserId]))}>}[Withdrawals.BaseAmount])
Which returns an 'Error in expression'.
Managed to come by the right solution:
Sum(if(Date([Withdrawals.PaymentDate]) <= Aggr(nodistinct Aggr(nodistinct min([Deposits.PaymentDate]),UserId),[Withdrawals.UserId]) + SomeDays, [Withdrawals.BaseAmount]))