6

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.

Big Chungus
  • 145
  • 1
  • 9
  • 3
    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 Aug 09 '19 at 00:20
  • 1
    Any news on how to solve this on QuickSight level without touching the database? – perelin Aug 23 '19 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 Aug 26 '19 at 20:06

0 Answers0