Rolling 4 week average formula based on distinct_count

Question & Answer
1

Hi exeprts,

I have a fairly complicated formula I’m trying to accomplish in QuickSight.

Assuming we have a created_date column that we aggregate at the week level (starting on Sundays). How would we sum up all distinct count values of an ID over the previous 4 weeks (not including the current week) divided by 4 (number of weeks)? We’re trying to get an average distinct_count of ID’s over a 4 week block (not including the current week).

In the visual, we wan this period of dates and we want to add up all the id values and divide by the number of weeks (4). Help! TIA.

image
image748×773 49.2 KB

2

Can you do something like this?

runningSum
(
distinct_count(ifelse(truncDate(‘WK’,initiationtimestamp)=truncDate(‘WK’,now()),NULL,contactid))
,[ initiationtimestamp ASC]
)/distinct_count(ifelse(truncDate(‘WK’,initiationtimestamp)=truncDate(‘WK’,now()),NULL,truncDate(‘WK’,initiationtimestamp)))

3

Hey Max,

I tried to paste this into a calculated field, and it’s throwing an error on the section:

distinct_count(ifelse(truncDate
4

Hmm, it’s working for me.

Have you substituted your fields in?

5

Yes, just for initiationtimestamp and contactid, right?

6

yes. Are you sure your fields are string and datetimes for id and datetime respectfully?