In the final week of ‘alternative charts month’, Luke set this challenge as different way of presenting data that you might typically see in a side-by-side bar chart.

Luke had indicated on the #WOW splash screen, that this challenge was ‘easy’, but that’s always dependent on your level of Tableau. He also added a note in the requirements that if you wanted to be ‘advanced’ to solve it with Table Calcs only.

I figured I’d just start and see what I ended up with (sometimes, my natural brain thinking takes me down a table calc route..)

In a change to my usual starting point, I started trying to remember what I needed to do to get the comet… I felt pretty sure that **path** would be involved somewhere.

So, I added **Order Date** to the **Filter **shelf and filtered to years 2021 and 2022 only.

Then I added **Sub-Category** to **Rows**, **Sales** to **Columns** and **Order Date** (which defaulted to YEAR(Order Date)) to **Detail**. I changed the **mark type **to **circle **initially.

Ok – I had what I was expecting – 2 circles per row, one for each year.

So then I change the **mark type **to **line** and moved **YEAR([Order Date]) ** from **Detail** to **Path.** This meant my lines were joined.

I then added **Order Date** to **Size**, and reset Order Date to be at the **YEAR** level. Hey presto! My comet shapes appeared.

I now wanted to show a white circle mark just for the 2022 sales, so I created

**Max Year Sales LOD**

{FIXED [Sub-Category]:SUM(IF YEAR([Order Date]) = {FIXED:MAX(YEAR([Order Date]))} THEN [Sales] END)}

this looks a bit long-winded ( I do usually break this up)… so let’s review what’s going on…

{FIXED:MAX(YEAR([Order Date]))} returns the latest year in the data set (ie 2022) and spreads that across every row of data. So the formula is comparing each row, and if the **Order Date** year matches 2022, the value of the **Sales** is returned. This is then all aggregated and totalled for each **Sub-Category**.

Add this field to** Columns**, make **dual axis** and **synchronise axis.**

Remove **Measure Names** from the **All** marks card and change the **mark type** of the **Max Years Sales LOD** card to circle. Colour white.

Remove the YEAR([Order Date]) pill from the **Size** shelf of the **Max Years Sales LOD** card, so the size of the comet (the **Sales **card) and the circle can be adjusted independently. Adjust the sizes enough so the comet is visible around the circle.

Sort the **Sub-Category** field by **Max Year Sales LOD** descending

Next we need to colour the comets based on whether **Sales** increased or decreased.

**Prev Year Sales LOD**

{FIXED [Sub-Category]: SUM( IF YEAR([Order Date]) = {FIXED:MAX(YEAR([Order Date]))} -1 THEN [Sales] END)}

is doing similar to the above calculation, but {FIXED:MAX(YEAR([Order Date]))} -1 returns 2021 instead.

and with this we can created

**Sales Increased? LOD**

[Max Year Sales LOD] > [Prev Year Sales LOD]

Add this to the **Colour** shelf of the **Sales** marks card, and adjust accordingly.

To label the comets, check the **show mark labels** checkbox on the **Label** shelf dialog, and set to **line ends** and **label end of line**. You may need to check the **allow labels to overlap** option too if you’re not seeing all the labels.

The dashboard shows a circular size legend which is related to the circle mark, so I created

**Order Date (Years)**

YEAR([Order Date])

and added this to the **Size** shelf of the **Max Year Sales LOD** marks card.

Add **Sales** to the **Tooltip** shelf of the **Max Year Sales LOD** marks card too and adjust the tooltips.

Add row dividers, and remove all column dividers, gridlines and axis. Adjust the formatting of the **Sub-Category** row labels and hide the column title. Set the background of the worksheet to a grey colour.

And so that ended up being the LOD version of the chart, which is accessible from here.

But I had time, so I figured I’d see if I could crack the Table calcs version…

Building the Table Calculation Solution

This starts by repeating the intial steps above to get a basic single axis comet chart for Sales, split by Year.

We now need to get the sales for 2022 only. For starters, let’s identify the latest year

**Latest Year**

WINDOW_MAX(MAX(YEAR([Order Date])))

and let’s build up a table, so we can start to sense check what’s going on, as table calcs can be pesky!

Our **Latest Year** table calc is returning 2022 for every row in our table. To get the sales just for 2022

**Window Max Year Sales**

WINDOW_MAX(IF MIN(YEAR([Order Date]))=[Latest Year] THEN SUM([Sales]) END)

if the order date year is 2022, then return **Sales** (otherwise null) and spread the maximum value across the rows. When we add this into the table, we need to set the table calculation to compute using **Year or Order Date**, so that it is calculating the WINDOW_MAX for each **Sub-Category**

Add this field to **Columns** on the comet chart, and adjust the table calculation so **Window Max Year Sales** is computing by Year of Order Date only, and **L****atest Year** by both fields (see the Nested Calculations dropdown)

Make the chart **dual axis**, and **synchronise the axis**. Make the adjustments to the mark types and sizes as described above.

We can’t sort the **Sub-Category** field in the way we did above, as table calculation fields aren’t accessible in the sort dialog. Instead add **Window Max Year Sales** to **Rows** and change it to be **discrete **(blue pill) and move it to be in front of **Sub_Category**. Adjust the table calc settings to match that described above. This should make the chart sort ascending.

To reverse, double click into the blue** Window Max Year Sales** pill on **Rows** and add * -1 to the end

Annoyingly this will revert it back to a measure, so reapply the steps above, and you should end up with a correctly sorted display. Hide the **Window Max Year Sales** blue pill.

Now to colour the comet.

Back to the tabular view. Add a **Difference **quick table calculation to the **Sales** pill and edit the table calculation to compute using **Year(Order Date) ** only.

Drag the **Sales** pill with the difference table calc from the Measure Values section and drop into the left hand data pane. This will create a dedicated instance of the calculation. Rename it to

**Sales Diff – TC**

ZN(SUM([Sales])) – LOOKUP(ZN(SUM([Sales])), -1)

If you examine it, it should contain the above calculation.

With this we can then work out if sales have increased or not

**Sales Increased? TC**

WINDOW_MAX(IF [Sales Diff – TC] > 0 THEN 1 ELSE 0 END)

Adding this into the table, and setting the nested table calcs to both compute by Year Order Date, you can see that the values for each **Sub-Category** are either 1 or 0.

Add this to the **Colour** shelf of the **Sales** marks card. Make sure the field is discrete and the table calcs are set to compute by Year Order Date. Adjust the colours.

Finally make adjustments for the tooltip and adjust the formatting to clean up the chart. My table calc version of the viz is here.

Happy vizzin’!

*Donna*