Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi again,
Same issue I posted Friday but I will be more specific this time. I have this data:
UserId | ActionId | Date |
---|---|---|
1 | 1 | 1/1/2018 |
1 | 2 | 1/1/2018 |
1 | 2 | 2/1/2018 |
2 | 3 | 3/1/2018 |
2 | 4 | 4/1/2018 |
And I want a filter that will yield the following:
Count Instances from FirstDate to 2/1/2018
UserId | ActionCount |
---|---|
1 | 3 |
2 | 0 |
You might just be looking for set analysis here
Dimension
UserId
Expression
Count({<Date = {"$(='<=' & Max(Date))"}>}UserId)
Thank you for the reply Sunny, however, this is yielding a column full of NULL values.
I managed to get the filter working when all the values belong to the same table:
Sum(if([Deposits.Date] <= Aggr(nodistinct 14 +
min([Deposits.PaymentDate]),[Deposits.UserId]),[Deposits.BaseAmount],0))
Legend:
Sum(if([Deposits.Date] -> Date of the event as the 'Date' feature of my first example.
Aggr(nodistinct 14 + min([Deposits.PaymentDate]),[Deposits.UserId]) -> Date 2 weeks (or 14 days) after the PaymentDate.
[Deposits.Amount], 0 -> If first date is smaller or equal than Max Date, sum deposit amount, else 0.
(Any notes to improve that query will be much appreciated).
However, when I mix tables i get wrong results, in this case a column of zeroes, like such:
Sum(if([Withdrawals.Date] <= Aggr(nodistinct 14 +
min([Deposits.PaymentDate]), [Deposits.UserId]), [Withdrawals.BaseAmount],0))
Why is that?
I want to filter all data (not only the one from the deposit table) based on X amount of time after the Deposit.Date.
Mix tables? Would you be able to elaborate on this by providing a sample?
Sure
I get the 'MaxDate' through the Deposit table, where I have DepositId, DepositDate, UserId...
And when I filter through actions on that table, such as 'Deposit.BaseAmount' between dates on the example above, the filter works fine. Even-though I am pretty sure the function Aggr is not the most optimal way to approach the filter.
However, when I try to filter through actions located in another table, such as 'Withdrawals.BaseAmount', which is on the Withdrawal table, where I have WithdrawalId, WithdrawalDate, UserId... The filter returns non-accurate results.
Meaning that I am comparing 'Withdrawals.Date' to
Aggr(nodistinct 14 + min([Deposits.PaymentDate]),[Deposits.UserId])
which is a Date 14 days larger than when they made the first deposit.
Any tips?