Aggregate to make calculation dynamical

[annabergendal]

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?

[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]

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]

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

[annabergendal]

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]

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 · Accepted solution]

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.