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

4

Have you tried adding the date time to the column?

And why does it not work if you have?

image
image1736×1097 63.1 KB

5

Hi @LionelIS, Please let us know if Max’s suggestion helped.

Regards,
Karthik

6

Hi Karthik,

Max’ suggestion did not really help me for my case (but very handy).
In fact, I did not formulate correctly my question:

I want some calculated values from my data for this month and this week (reference date = now). Obviously, “week” values are a subset of “month” values (i.e. included in “month” value).

Max’s answer is calculations partitioned over a field, i.e. exclusive, the calculations I want are cumulative. My solution (not sure it’s the best one) give me the correct values I was looking for and is as follow.

1- Create a calculated field “metricDate_UTC_segment”:

ifelse(truncDate(‘MM’,{metricDate_UTC}) >= truncDate(‘MM’, ${Today}), 1, 0)
+
ifelse(truncDate(‘WK’,{metricDate_UTC}) >= truncDate(‘WK’, ${Today}), 1, 0)

Where 0 = out of date range, 1 = inside largest date range (month), 2 = inside 2nd date range (week).

2- New calculated fields using runningSum, runningAvg, ex.:

runningSum(distinct_count(conversationId), [{metricDate_UTC_segment} DESC])

runningAvg(avgIf(value, metric = ‘tHandle’), [{v0.0 metricDate_UTC_segment} DESC]) / 1000.0

3- New calculated field for getting date range instead of 0, 1, 2, Pivot table with:

  • Rows: Date range, metricDate_UTC_segment (hidden, and level 0 hidden)
  • Columns: my calculated fields (point 2)

imagen

Got what I wanted!

Now If I may, as you’re from QuickSight team, one comment / suggestion:

I obtained the same results at the beginning, very quickly, with simple functions like distinct_count, avgIf and adding 2 visual with different filters based on parameters. Cool, easy, quick, but ugly: impossible to merge the 2 visuals, swap rows/columns gives an undefined/non editable column header:
imagen

QS feature suggestion: adding filter for individual column (and row?) not just by visual.

Best,
Lionel

1 Like
7

I’ll mark this as a feature request