Plot chart with segmentation quick filters

MrUziel

I'm building a plot chart, where the x axis is the number of times something has been used and the y axis is the amount of money they've paid.

 

I can simply group this information by quarter and year, which is fine. However there are different kinds of usage, and people want to be able to filter to certain quarters, type of event, and the customer. I've tried organizing the data two ways to get this, and neither seems to be working.

 

Solution 1

Company | Year | Quarter | $ Amount | Total Events | Count of Event 1 | Count of Event 2 | Count of Event 3

 

The problem with this is that I can't have a filter that segments out unwanted events; it's total or nothing, or they have to mofiy the graph themselves to the corresponding event. There's no way to get hte quick filter to do this for them.

 

I've found an alternate solution that sort of looks like this:

Company | Year | Quarter | $ Amount | Type of Event | Number of Events

BobCo     | 2017 |      1      |   100.00   | Event Type A  | 422

BobCo     | 2017 |      1      |   100.00   | Event Type B  | 201

BobCo     | 2017 |      2      |   100.00   | Event Type A  | 488

BobCo     | 2017 |      3      |    50.00    | Event Type A  | 407

 

This almost works. I can segment on event type and quarter just fine, and using a beast mode sum(distinct(amount)) gets me pretty close to my original plot chart. However, some quarters have the same amount; they paid that money, but because it's the same as a previous quarter, it isn't counted. I want all quarters to be counted, but I don't want quarters being counted twice. Basically I want sun(distinct-per-quarter(amount)) but I'm not sure how to make that happen.

 

This way, people can look at, say, a particular year and a particular event, and see how much they paid that year and how many times they used that feature, using the quick filters.

 

Thanks!

MrUziel

I figured it out.

 

The solution I ended up going with was having the amount for the quarter as its own row, and all the other rows for the quarter be filled with '0'. Ifnull(`Amount`,0) worked just fine to fill in null values.

MrUziel

I figured it out.

 

The solution I ended up going with was having the amount for the quarter as its own row, and all the other rows for the quarter be filled with '0'. Ifnull(`Amount`,0) worked just fine to fill in null values.