topic Re: Aggregate to make calculation dynamical in QlikView

Aggregate to make calculation dynamical

annabergendal — Mon, 11 Jun 2018 13:00:18 GMT

I have a dataset with transactions per person.

I would like to know how many people have more than 12 transactions last 13 months, lets call them “frequent buyers”.

I managed to precalculate number of transactions last 13 months in the script:

load

PERSONID,

count(distinct TRANSNUMBER) as Total

Resident

FactTransaction

where Date >= addmonths(today(),-13)

group by

PERSONID;

And then use it in the set analysys

count({$<Total ={">=12"}>} distinct PERSONID)

Only problem is that I need it dynamically, calculated front-end, so that the user can select a year-month and have it calculated 13 months back from that time period...

I need to do some aggregation I guess, and ideas how?

Re: Aggregate to make calculation dynamical

sunny_talwar — Mon, 11 Jun 2018 13:18:13 GMT

May be this

Count({<PERSONID = {"=Count({<Date = {[$(='>=' & Date(AddMonths(Max(Date), -13), 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))]}>}DISTINCT TRANSNUMBER) >= 12"}>} DISTINCT PERSONID)

Updated the inner count to include Distinct count the transnumber just like your script

Re: Aggregate to make calculation dynamical

lironbaram — Mon, 11 Jun 2018 13:23:54 GMT

something in this lines should work for you

sum(aggr(if({<Date ={">= $(=addmonths(max(Date),-13))">}count(distinct TRANSNUMBER)>12,1,0) ,PERSONID))

Re: Aggregate to make calculation dynamical

annabergendal — Mon, 11 Jun 2018 13:48:37 GMT

This one gave me simply "0", maybe I made som syntax error...

Re: Aggregate to make calculation dynamical

annabergendal — Mon, 11 Jun 2018 13:49:23 GMT

Syntax error after "if", can you really include if in set analysis?

Re: Aggregate to make calculation dynamical

sunny_talwar — Mon, 11 Jun 2018 13:50:26 GMT

Can you share an image of the expression you used?

Re: Aggregate to make calculation dynamical

annabergendal — Mon, 11 Jun 2018 13:53:25 GMT

It's a copy of your expression, I need to check format of date maybe

Re: Aggregate to make calculation dynamical

sunny_talwar — Mon, 11 Jun 2018 13:56:59 GMT

It's a copy of your expression

Not sure I understand.... I was looking for an image of the expression in the expression editor from your app

Re: Aggregate to make calculation dynamical

marcus_sommer — Mon, 11 Jun 2018 14:34:44 GMT

Maybe this simple approach fits better:

if(count(distinct TRANSNUMBER)>=12, count(distinct PERSONID))

- Marcus

Re: Aggregate to make calculation dynamical

annabergendal — Mon, 11 Jun 2018 21:18:37 GMT

Thank you all for your suggestions.

I ended up using this syntax:

sum(if(aggr(count({$<Date={">=$(=vMaxDate)"}, Transdatum={"<=$(=vMaxDate)"}>} distinct TRANSNUMBER),PERSONID)>=12,1,0))

So it was similar to what you suggested Liron Baram but another order with if-statement.

Also I created variables for timeperiod.