Time Filter (2)

[Anonymous]

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

[sunny_talwar]

You might just be looking for set analysis here

Dimension

UserId

Expression

Count({<Date = {"$(='<=' & Max(Date))"}>}UserId)

[Anonymous]

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.

[sunny_talwar]

Mix tables? Would you be able to elaborate on this by providing a sample?

[Anonymous]

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?