Qlik Community

Ask a Question

App Development

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

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
caixaofaixa
Contributor II
Contributor II

Time Filter (2)

Hi again,

Same issue I posted Friday but I will be more specific this time. I have this data:

UserIdActionIdDate
111/1/2018
121/1/2018
122/1/2018
233/1/2018
244/1/2018

And I want a filter that will yield the following:

Count Instances from FirstDate to 2/1/2018

UserIdActionCount
13
20
4 Replies
sunny_talwar

You might just be looking for set analysis here

Dimension

UserId

Expression

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

caixaofaixa
Contributor II
Contributor II
Author

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?

caixaofaixa
Contributor II
Contributor II
Author

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?