Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

Aggregate to make calculation dynamical

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?

1 Solution

Accepted Solutions
Partner
Partner

Re: Aggregate to make calculation dynamical

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.

9 Replies

Re: Aggregate to make calculation dynamical

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

Partner
Partner

Re: Aggregate to make calculation dynamical

hi

something in this lines should work for you

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

Partner
Partner

Re: Aggregate to make calculation dynamical

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

Partner
Partner

Re: Aggregate to make calculation dynamical

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

Re: Aggregate to make calculation dynamical

Can you share an image of the expression you used?

Partner
Partner

Re: Aggregate to make calculation dynamical

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

Re: Aggregate to make calculation dynamical

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

MVP & Luminary
MVP & Luminary

Re: Aggregate to make calculation dynamical

Maybe this simple approach fits better:

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


- Marcus

Partner
Partner

Re: Aggregate to make calculation dynamical

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.