Then click on the Axis tab and set the Dates value in the Scale section to the same custom mmm yy value. On the Format Date pane on the left hand side, set the Dates value on the Pane tab, in the Default section, to custom : mmm yy. To format the Date field within the tooltip and the axis, right click on the Date pill in Columns and select format. Now rearrange the text on the label so that company is displayed first. In the formatting pane that appears on the left hand side, change the number formatting to custom ▲0.0% ▼0.0% ( I use this site to get the shapes I need – just copy and paste) To format the % value displayed, right-click on the % Diff from Selected Date pill on the Text shelf, and select format. To make the text match the mark colour, click Label >then expand the Font dialog by clicking the arrow to the right, and select Match Mark Colour. Add Company to Colour and adjust to suit (use the hue circle colour palette).Īdd Company to the Label shelf and also add % Diff from Selected Date to the Label shelf.Īdjust the Label settings, so the labels only display at the end of the line. On a new sheet add Date to Columns and set to the continuous Month level or exact date (either will work in this instance, since we only have 1 date per month in the data set). The only thing to remember is when adding the table calc fields, set the compute by, and if you’re using the LoDs, set the aggregation to AVG (although SUM will work too due to the level of aggregation we’re at). I am now just going to refer to the calculated field names I’ve mentioned above and not differentiate between LoD or table calc version. So now we have the core fields we need to start building the viz. Again if you’re using the table calc version, you need to verify the calculation is set to compute by Date only. Once again I am choosing to show both versions on the screenshot below. Tableau will error if you try to do this). There is a slight difference in the syntax as the table calc option is referring to a field that is a table calculation and which is already aggregated, so there’s no need to aggregate again (ie wrap in an AVG…. Now that for each row we have an average Close value and the average Close value on a specific date, we can work out the percentage difference for each row. Test by entering different dates in the parameter (though make sure you’re just entering 1st of month each time, as that is what our Date field contains). Scrolling down, you should be able to verify that the value displayed in the new field(s) for all rows for a specific company, matches the value in the first column for the selected date. In the below screen shot, I have included both versions of the calculation which match each other. If you’re using the Table Calc option, change the table calculation so that it is computing by Date only. If you’re using the LoD option, change the aggregation to AVG (although as we’re working with the data at the lowest granularity, SUM gives the same value, it just makes ‘more sense’ to me). WINDOW_MAX(AVG(IF =DATETRUNC(‘month’,) THEN END))ĭrag this field onto the table. I now want to work out what the value of the average Close is on the selected date (in this case ) for each company, and I want this value to be displayed against every row associated to that company. To start add Company then Date (set to exact date & discrete) to Rows then add Close to Text and change the aggregation to AVG. Show this on the worksheet, so you can test what happens when you manually change it later.Īs I typically do, I’m going to work in a tabular format to sense check all the calculations we’re going to need before I start visualising the information graphically. I hard coded/defaulted this to 1st Nov 2020. Hopefully, it’ll all make sense □Īfter connecting to the data, the first step I did was to create a parameter to store the ‘selected date’. As I blog I will endeavour to provide both sets of calculations used and make it obvious when I do so. As is often the case in Tableau, there can be multiple ways to solve a problem, so I created another solution involving table calcs. Lorna Brown returned this week with her first challenge of 2022 which was aimed at practising tableau calculations, although when I completed the build, I realised I actually didn’t use any – oops! I’d ended up using an LoD based solution instead.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |