Is it possible to customize duration granularity in question in QuickSight Q?


I have a QuickSight Q topic relating to members of a subscription. The key fields in this topic related to my question are:

  • Member ID
  • Activation Date (the time the subscription began)
  • Cancellation Date (the time the subscription ended)

I also have a calculated field called “Membership Length” which calculates the length between the activation date and the cancellation date, or if the member is still subscribed, between the activation date and the current time. Importantly, this calculation is done in days. Here is a screenshot of how this field is configured:

I am able to successfully use these fields to ask Q questions like “Show me the top 20 members by their membership length”. This works as I would expect:

My goal is to tell Q that I want to show this data in months, rather than days. I figure this should be possible given that the calculated field for Membership Length has its Semantic sub-type set as “Day”. However, when I ask the question “Show me the top 20 members by their membership length in months”, the result is still shown in days.

Is there something I am doing wrong? Is there a way that I can better configure the Membership Length field to achieve this goal? Or perhaps is there a better way to phrase the question so that Q can better understand I am hoping to alter the granularity of the duration?

I appreciate any guidance on this.

Hi @TheCardlessMoose

Indeed Q is not able to convert from days to month at runtime. One alternative would be to calculate difference in the dates in months. This could be done one of 2 methods.

1- in new field - divide days by 30
2- in new field - calculate datediff selecting period as “MM” to calculate in months.

hope this helps!
Ramon Lopez