distinctCountOver with date-ranges from parameters

Question & Answer
, ,
#1

Hi,

I need to calculate (calculated field) a distinct count partitioned by several data-range specified by parameters (rolling dates), but cannot so far find the right way to do it, or get useful advice from web.

My “best” guess so far is:
distinctCountOver({Id}, [${1stOfThisMonth}, ${SundayOfThisWeek}])

where 1stOfThisMonth and SundayOfThisWeek are parameters (rolling date from 1st of the month / last Sunday to now, which work well as filters for visual)
but QuickSight just says “Errors found” (no explanation, so useless).

Any advice would be welcome.
Thanks,
Lionel

#2

I think you need a little bit more.

A parameter is a constant so what you are saying right now is that you want a disinctCountOver everything in your datasets. Because for all rows these parameters are the same value.

Here is what I have done and let me know if this helps.

First I made a calculated field that checks to see where my date is before or after a parameter.

before_first_sunday = ifelse({arrival_timestamp}<${1stSunday},‘Before’,‘After’)

Then I made a disinctCountOver field paritioned by the before_first_sunday.

distinctCountOver({id},[{before_first_sunday}],PRE_AGG)

Let me know if that’s what you’re looking for

#3

Hi Max,
It’s not what I’m looking for, maybe my word “partitioned” is not the correct one.
In your example, if I understand well, you will get unique Id counts for “before” and “after”.

I try mines calculated fields:
This_week = ifelse( truncDate(‘WK’, {metricDate_UTC}) >= truncDate(‘WK’, ${Today}), {metricDate_UTC}, NULL)

test = distinctCountOver(conversationId, [{This_week}], PRE_AGG)
No error when saving, but when adding a table with “test” I get “Your calculated field contains invalid syntax”.

Actually, what I’m looking for is a table [Headers, this_month, this_week] like:
imagen

Obviously, values for this_week are also included in this_month (so not really partitioned on one field).
I obtained the above fig by adding 2 visuals (tables) with 2 filters (month, week) and values are correct, but it’s a bit tedious and clumsy for aligning and title, fine for 2 columns but not with 10!
I 'm looking for a more automatic and clever way to get a single multi-column table.

Thanks for your help