How to configure a Looker Studio Line Chart to display only dates from my Google Sheet?

[TechBill]

Hello,
I'm using Looker Studio and I have a Line Chart that pulls data from a Google Sheet. The issue I’m encountering is that the chart displays every date in a month (1 to 31), even when my data is only recorded on specific dates (e.g., once or twice a week). Most of these dates show a value of zero, which I want to avoid.

I’ve already tried adjusting the date settings under the 'Setup' tab, including the 'Date Range Dimension' and 'Dimension,' but the chart still shows all the dates, not just those present in the sheet.

Is there a way to configure the chart to display only the dates that are actually in my Google Sheet, without showing empty or zero-value dates?

Thanks in advance for any assistance!

[kdimitrof · Accepted solution]

Hi there,

Not sure if there is such an option, however I usually use a different line chart to avoid displaying dates that are not existing in my data table, like the example below,

Kind regards,
Krassi

[TechBill]

I tried your suggestion and it somewhat what I am looking for but it seems to set the chart date out of order by using the data which begins with lowest number to highest number.  

How do I set it that it follow the order of the dates not the data numbers in chart?

[marcwo]

Hi @TechBill 

a time series chart is by design shwoing how data changes over time and if you select date as granularity, it will show all dates with their respective value. Now that dates are missing in your case, the chart will default to zero as there are no values for the selected metric.

You have a few options here:

- if you want to continue using a time series chart, you can change the behavior for those dates - under Missing Data described you can choose how the visual behaves for those dates, it wont remove them though.
- change the granularity: if you are good to sum this up per week, change your granularity from date to week and you will see a sum per week, that wont give you any zero values if you have at least one value per week

If you are really looking for the scenario you are describing, just select a line chart or column chart alternatively. Use your date as the dimension, the metric as metric and you will only see values for the dates you have. One thing to be cautious of though: you need to set the number of columns if using column chart, because per default it only displays 10 columns.

Thanks
Marc

[TechBill]

Sorry, I did not reply sooner .. strangely Gmail marked this email notification as a dangerous spam until I took a closer look and noticed it was a notification from Google own community! LOL!  I am glad I double checked!