Example for windowCount

Question & Answer
,
1

hi,

I didn’t really get the point of windowCount function and the aws documentation wasn’t really clear.
Can someone send me a good example of this function usage?

Thanks,
Amihai

2

windowCount would allow you to specify a ‘lookback’ window of how many data points prior (or after) you want to count. If you want to count all the data points in a given partition I would use countOver instead.

Say you have a line chart with at a daily level, but you want to count the records from the last 5 days for each period instead, you could use windowCount for that. Agree it’s not the most common function to use.

3

thanks for your answer.
I would like to advise on a specific issue-
Let’s say I have the below dataset:

transId date amount user
100001 12/06/2022 61 AAA
100002 10/06/2022 2 AAA
100003 08/06/2022 39 null
100004 08/06/2022 9 BBB
100005 07/06/2022 100 BBB
100006 07/06/2022 8 CCC
100007 04/06/2022 94 AAA
100008 02/06/2022 58 null
100009 02/06/2022 12 null
100010 31/05/2022 46 BBB
100011 30/06/2022 68 CCC
100012 30/06/2022 89 DDD

I would like to count distinct the number of users in a window of 7 days backwards.
the result will be for 12/06 & 05/06 dates for example-

date usersCountd
12/06 3
05/06 4

Thanks,
Amihai

4

Hi,

Create a calculated field in the dataset that calculates the last day of each week. I used this:
addDateTime( 1,‘WK’,truncDate(‘WK’,addDateTime( -1,‘DD’,date)))

Then you can just use simple CountDistinct in the Analysis

Adir

5

Hi,

Thanks for your answer.
I’m looking for a moving period of the last 7 days related to date value of the row.
Basically it can be any period length… 14,21,30 days. I took 7 days only as exampke.
This is the reason I mentioned a window function and not a countDistinct.

thanks,
Amihai

6

HI Amico - if you want to display the distinct count of a given window as a single number (like a KPI) that is definitely possible, but if you want to show it in a table with multiple dates in it (each with a window relative to that row) we don’t have a way to do this today. We’ve passed your feedback to the product team to implement something like windowDistinctCount.

7

Would love to see a windowDistinctCount function as I would use this to determine monthly customer movements and need the previous month to achieve this. I noticed there is also another similar post asking how this can be achieved.

Can this be achieved using calculated fields?