# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for
Did you mean:
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:

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
Author

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
MVP

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

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
Author

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

Partner
Author

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

MVP

Can you share an image of the expression you used?

Partner
Author

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

MVP
 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

Maybe this simple approach fits better:

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

- Marcus

Partner
Author

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.