Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
annabergendal
Partner - Creator
Partner - Creator

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
annabergendal
Partner - Creator
Partner - Creator
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.

View solution in original post

9 Replies
sunny_talwar

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

lironbaram
Partner - Master III
Partner - Master III

hi

something in this lines should work for you

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

annabergendal
Partner - Creator
Partner - Creator
Author

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

annabergendal
Partner - Creator
Partner - Creator
Author

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

sunny_talwar

Can you share an image of the expression you used?

annabergendal
Partner - Creator
Partner - Creator
Author

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

sunny_talwar

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

marcus_sommer

Maybe this simple approach fits better:

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


- Marcus

annabergendal
Partner - Creator
Partner - Creator
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.