# App Development

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for
Did you mean:
Contributor II

## Issue with measure

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.

1 Solution

Accepted Solutions
Contributor II
Author

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]))
5 Replies
Champion

Hi,

it is better to share sample app or sample data

Contributor II
Author

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

Champion

EDITED

try this:

sum({<[Withdrawal Date]={"<=1/1/18"}>}Amount)

Contributor II
Author

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'.

Contributor II
Author

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]))
Tags
Community Browser