topic Re: Filter multiple transactions within time period in QlikView

Filter multiple transactions within time period

jono19872002 — Wed, 22 Jan 2020 16:42:35 GMT

I need to create a table that shows all customers who have had more than 1 transaction within 6 months, but am a bit stuck where to start?

Ideally this would be done in Qlivkiew with a function rather than in the script as it is someone elses app which I want to tag onto

Not sure if I can use the AGGR function some way, but cant quite work it through how to structure this

Re: Filter multiple transactions within time period

Kushal_Chawda — Wed, 22 Jan 2020 16:46:10 GMT

Can you post the snapshot of your data?

Re: Filter multiple transactions within time period

jono19872002 — Wed, 22 Jan 2020 16:51:31 GMT

Hi,

To simplify, assume I have got 3 fields, Customer, SaleDate, Value.

I want a simple table to show all customers who have had a second (or multiple) SaleDate(s) within 6 months of each other

So in the attached picture of sample data I just want to show customer C and both the dates

Re: Filter multiple transactions within time period

Kushal_Chawda — Wed, 22 Jan 2020 20:54:12 GMT

create straight table with Dimension Customer & SalesDate and below expression

=sum({<Customer={"=(min(total <Customer> SaleDate)-max(total <Customer> SaleDate)) and min(total <Customer> SaleDate) >= addmonths(max(total <Customer> SaleDate),-6)"}>}Value)

Note: This will work only when there is just two transactions. If you have more than 2 transaction then please share the scenario with expected output

Re: Filter multiple transactions within time period

Brett_Bleess — Wed, 19 Feb 2020 17:30:17 GMT

If Kush's last response was what you needed, please be sure you return to your thread and on his post, use the Accept as Solution button to mark the post as the solution, which will give him credit for the assistance and let other Members know it did work for your use case. We appreciate members closing out their threads. If you are still working on things, please leave an update.

The one place I can point you for future assistance is the Design Blog area, there are over 700 posts here, mostly how-to from our internal experts, I am providing a specific one on set analysis and then the base URL to the site so you can search further if you wish.

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett