topic Sort AGGR to find Purchase Frequency in QlikView

Sort AGGR to find Purchase Frequency

lfogoncalves — Wed, 28 Oct 2015 22:00:03 GMT

Hello,

I'm trying to find a way to calculate the frequency of payment about the selections that have been made.

I have a Fact table with invoices for many customers and my goal is to find this:

Client A - Buys every 5 days.

Client B - Buys every 10 days.

etc.

I've been trying with the Aggr() but i'm not being able to sort the table so that the invoice dates make sense.

Does anybody deal with this? In the attach is what i want to do but removing the ID_Date from the table.

Re: Sort AGGR to find Purchase Frequency

swuehl — Wed, 28 Oct 2015 22:21:07 GMT

Try a straight table with dimension Store Code and a single expression

=(Max(Id_Date)-Min(Id_Date))/ count(DISTINCT Id_Date)

Re: Sort AGGR to find Purchase Frequency

lfogoncalves — Wed, 28 Oct 2015 22:24:07 GMT

It worked. I was thinking the avg(aggr(avg(Id_Date)-above(avg(Id_Date)),[Store Code],Id_Date)) approach but it was forcing me to sort the table. It can be a table with hundreds of millions of rows so i think your approach is the best.