Linechart : show previous value when date is missing

Question & Answer
,
1

Hello everyone !

I’m trying to create a cumulative linechart on Quicksight and displaying several categories.

For the example, let’s say that I use a dataset of all the users and I want to see how the number of users in my database evolves along time in different coutries.

id,username,joiningDate,country
1,Dams,'2023-07-12','br'

Then to create a cumulative linechart, I would just have to create a calculated field :
c_cumulated :

runningSum(
    count(id), 
    [joiningDate ASC], 
    [country]
)

This will give me a nice cumulative line chart, but if there is a gap in data of seral days / months, QuickSight will just fill in the gaps with a simple line. That’s not entirely correct, we can’t have half of a user for a day / month.
The solution for this would be to tick the “Show date gaps” checkbox in the “Format visual section”, but then we only have 2 options:

  • Show a broken line
  • Show the line as zero

None of these options are valid for a cumulative linechart, I would need the missing date value to use the last available data.

I couldn’t find a work around for this, but you might be able to help me.
If not, I guess that could become a new feature request.

Thank you for your time, have a good day

2

Hi,

This is a highly requested feature request.

The only other way to work around this would be to use sql to make a calendar and join your data onto those dates and if it’s null then make it 0 so it’s still available but doesn’t count towards anything.