Creating Aggregate Category:Value data from dataset for subsequent visualization use

Question & Answer
, ,
1

This is a question about aggregating and then visualizing data based on an existing Quicksight dataset.

Suppose you have a transactional dataset (potentially tens/hundred thousand rows) and want to create a waterfall based on some categories that are defined by their own calculated fields, similar to a map structure. E.g.

New Customer Sales: SUMIF({Revenue}, CustomerClass = “New”)
Discontinued Sales: SUMIF({Revenue}, ProductClass = “Discountinued”)
Standard Sales : SUMIF({Revenue}, ProductClass = “Active”)
etc. etc.

Can one create a waterfall chart with each of these calculated fields as their own category? It doesn’t seem to me that this is supported by Quicksight currently.

Please note that the above example could easily be created by just adding the ProductClass to the category field wells and revenue to the value but that assumes that each row will only contribute towards one category which is not necessarily the case.

In essence: Can one have a waterfall chart where each calculated field would be its own category label?

Any help is greatly appreciated!

2

It doesn’t seem like this is possible currently.

I can mark this as a feature request though.

Thanks!

3

Hi Max,

Thanks for responding! Do you given your experience see/know of any workaround to this problem? I was thinking that the fairly new parametrized custom SQL feature could potentially be relevant by moving the data adjustments to the dataset-layer while still allowing a user to dynamically select periods etc.

In general I suppose that there isn’t support for creating a “virtual table / map structure” that is then visualized in the typical way you would if you were using something like excel?

Any help would be really appreciated!

4

The workaround would be to have your sum of revenue and then color it by Product Class.

You might also need to union the customer class as the product class as well to show all of the new customer classes.

The one call out I would make sure you remember with the parameterized sql, is that the schema needs to stay the same. So like the column names needs to be consistent.