topic Aggregation in straight table in QlikView

Aggregation in straight table

Newbie — Wed, 05 Jun 2019 15:32:32 GMT

I have a requirement where I need to calculate total number of transactions for last 60 days keeping row transactions date as reference and then identify the transaction more than 3.

Customer,Date, amount,type
C1,01/01/2019,100,w
C1,26/01/2019,200,w
C1,02/02/2018,111,d
C2,01/05/2019,357,w
C2,04/05/2019,568,w
C3,09/03/2019,45,d

For each row I need total transactions happened in last 60days

E.g. for row 1 I need to get total transactions happened between 01/01/2019 and "date(01/01/2019) -60" i.e 2
Similarly for row 2 I need to get total transactions happened between 26/01/2019 and "date(26/01/2019) -60" i.e 1

Then finally show the records having 3 or more transactions.

Re: Aggregation in straight table

sunny_talwar — Wed, 05 Jun 2019 15:43:40 GMT

Why is this only done for the two dates? Why no for 01/05/2019 or 02/02/2018?

Re: Aggregation in straight table

Newbie — Wed, 05 Jun 2019 15:49:19 GMT

It has to be done for all records.I have given example of first two rows

Re: Aggregation in straight table

sunny_talwar — Wed, 05 Jun 2019 15:53:12 GMT

I think the best way to do this would to be use The As Of Table