Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
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:

PERSONID,

count(distinct TRANSNUMBER) as Total

Resident

FactTransaction

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 - 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.

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 - Master III

hi

something in this lines should work for you

Partner - Creator
Author

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

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

Community Browser