8

My visual's x-axis is Date, aggregated by month, and the y-axis is total sales (sum of costs). For some months, I had no sales, but Quicksight ignores these and only shows me the months that do have data in them, even though I want the exact number of months that I specify in my time range parameter.

So, when I ask to show the last 12 months, Quicksight shows me only 4, since for the other 8 I don't have any records.

How can I make those months show as 0, instead of being excluded altogether?

---------- EDIT (possible solution) --------------

Since my analysis relied on selecting a specific product code and only getting the sums for that, I took off the filter, and instead used a calculated field of:

sumIf(sales, productCode = $productCodeParam$).

I imagine this approach works on really small datasets too if you want to use sumIf and list all of your categories there, but otherwise, by omitting data with filtering you have nothing to result in 0, since Quicksight doesn't evaluate sum(null + null) into 0.

Improve this question
3
  • 4
    This is quite typical of systems that run SQL queries. Results only come back for data that exists. If you have access to the SQL-level query, you can join or union it to a table with zeros for each month and SUM them together.
    – John Rotenstein
    Commented Aug 9, 2019 at 0:20
  • 2
    Any news on how to solve this on QuickSight level without touching the database?
    – perelin
    Commented Aug 23, 2019 at 20:37
  • 1
    @perelin since my analysis relied on selecting a specific product code and only getting the sums for that, I took off the filter, and instead used a calculated field of sumIf(sales, productCode = $productCodeParam$). I imagine this approach works on really small datasets too if you want to use sumIf and list all of your categories there, but otherwise, by omitting data with filtering you have nothing to result in 0, since Quicksight doesn't evaluate sum(null + null) into 0.
    – Big Chungus
    Commented Aug 26, 2019 at 20:06

1 Answer 1

Reset to default
1

I came across this question while having the same issue today. And I think i found an answer, at least for me it works:

  • go to "Format visual"
  • select the x-axis menu
  • all the way at the bottom check the box for "Show date gaps"
Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.