The challenge for week 46 of # WOW2021 required creating a chart that allows you to compare sales at the Segment-Category level of the current year (CY) vs. prior year sales (PY) vs. the variation in sales between both periods.
Here are the steps I took to solve Lorna Brown’s challenge.
STEP 1. CALCULATE THE YEAR OF EACH SALE, THE LAST YEAR OF SALES ACCORDING TO THE DATA RECEIVED AND THE PRIOR YEAR.
| Year | YEAR([Order Date]) |
| Current Year | { MAX([Year]) } |
| Prior Year | [Current Year] – 1 |
STEP 2. CALCULATE THE SALES FOR THE CURRENT YEAR AND THE PRIOR YEAR.
| CY | IIF([Year] = [Current Year], [Sales], 0) |
| PY | IIF([Year] = [Prior Year], [Sales], 0) |
A copy of the PY campus is created. The field “PY (copy)” will be used in the construction of the variation bar.
STEP 3. CALCULATE THE AMOUNT OF THE VARIATIONS.
To calculate the variation, we subtract from the sales value of the current year, the sales of the previous year. Assign it a custom format: △ #, ## 0; ▽ #, ## 0
| Var | [CY] – [PY] |
Then use the Sign () function to determine whether the variations are positive or negative. This calculation will determine the color of the variation bar.
| Color | SIGN(SUM([Var])) |
STEP 4. DEFINE THE CHART.
Add Category to the Columns shelf and Segment to the Rows shelf.
Then add CY to the Columns shelf, which will create a bar chart. Then drag the PY field to the CY axis and when the “two columns” icon appears, release the field. This will automatically change the pills so that the measure values are in the columns and the measure names are in the rows.
In the “Measure Values” card, the menu is opened with the right click to add a new calculation. AVG(NULL) formula is captured.
This field will allow us a spacing between each Segment.
Add “Measure Name” to the Color shelf and “Measure Values” to the Label shelf. Set colors, font and text alignment is set to “Left.”
Proceed to hide the null indicator in the graph and the Header of the ”Measure Name” field.
The campuses “Current Year”, “Prior Year”, “CY”, “PY” and “Var” are added to the detail shelf, to have them both in the definition of the Sheet Title and for the Tooltips.
Subsequently, the “PY (copy)” field is added and indicate that it is a double axis synchronized with the “Measure Values” axis and hide both axes.
Go to the Second Axis card and change the chart type to Gantt.
Remove the “Measure Name” and “Measure Values” fields on the Gantt chart definition card.
Add the “Color” field to the color shelf and the “Var” field to the “Size” and “Label” shelves. Adjust colors and text.
READY!
Link here
I hope you enjoyed reading and let me know if you have any questions.
Rosario Gauna
RosarioViz 