Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
caixaofaixa
Contributor II
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
caixaofaixa
Contributor II
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]))

View solution in original post

5 Replies
YoussefBelloum
Champion
Champion

Hi,

it is better to share sample app or sample data

caixaofaixa
Contributor II
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

YoussefBelloum
Champion
Champion

EDITED

try this:

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

caixaofaixa
Contributor II
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'.

caixaofaixa
Contributor II
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]))

View solution in original post