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).
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.
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”.
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:
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.