Challenge, Stack, Workout Wednesday
By: Rosario Gauna @rosariogaunag
August in the month of the challenges of the Workout Wednesday community, for the second week of the month one of the challenges I sent was selected. Link to the challenge
A thank you to all those who participated and especially to those who had enough patience to finish it.
And since the Community is always a source of learning for me, I want to share that I found that, for the solution, we mainly divided into two large groups according to the technique used to obtain the coloring between the lines:
- Technique with Stack On. When the “marks” are accumulated, that is, when they are drawn cumulatively on the axis.
- Technique with Stack Off. When “marks” are not accumulated, that is, when they are drawn independently through the axis. That is, the “marks” overlap.
Then, with the learning achieved, I share a solution procedure to the challenge using both techniques, in addition to solving it, helping us to understand the main differences and similarities between the techniques.
Step 1: Define parameter “Select EFC or EFP”
So that the user can choose between:
- “EFConsPerCap” – Ecological Footprint of Consumption (EFC)
- “EFProdPerCap” – Ecological Footprint of Production (EFP)
Step 2: Define the “Filter” variable to filter the data.
Its value will be TRUE for all those records corresponding to the “Biological Capacity per Capita” and for those in which “Ecological Footprint per Capita” matches the one selected by the user in the parameter.
[Record]=”BiocapPerCap” OR [Record]=[Select EFC or EFP]
Step 3: Define the “Record Type” variable.
The variable will have only two values: 1) “Biological Capacity” and 2) “Ecological Footprint” regardless of whether it is its consumption or production value.
|Record Type =
IIF([Record]=”BiocapPerCap”, “Biological Capacity”, “Ecological Footprint”)
Step 4: Define the measures of Biological Capacity and Ecological Footprint
This in order to have an independent variable according to the record that is treated.
|Biological Capacity =
IIF([Record] = “BiocapPerCap”, [Total], 0)
|Ecological Footprint =
IIF(([Record] <> “BiocapPerCap”, [Total], 0)
Step 5: Define the variables of “Green Area”, “Red Area” and “White Area”.
According to the technique used, the formulas will be defined. You have to remember that in the:
- Technique: Stack ON- The “marks” are accumulated and they are drawn cumulatively on the axis. That is, the new “mark” starts where the last “mark” drawn on the axis ended.
- Technique: Stack OFF- The “marks” are not accumulated and they are drawn independently through the axis. That is to say, each “mark” begins to be charted in the zero value of the axis and the last one is superimposed over the previous ones in the area in which they coincide.
Step 6: Define the chart.
A chart with synchronized double axis will be used. The first chart will be of the area type and the second chart will be of the line type.
Move to the shelves of:
Indicate the option of Stack to use:
- Stack On. It is the automatic option in the “Analysis” menu – “Stack Marks” – “Automatic” option.
- Stack Off. In the menu of “Analysis” – “Stack Marks” select the option of Off.
According to the technique used, change the order in which the variables should be displayed. The key is in the orderin which the “White Area”is deployed.
- Stack On. “White Area” must be the first to be charted, so that it starts at the zero value of the axis and then the deltas of the red and green areas are plotted.
- Stack Off. “White Area” must be the last one to be charted, so that it covers or splices over the “Green Area” and the “Red Area” previously plotted. (The “Analysis” menu must be modified – “Stack Marks” to select the Off option)
Step 7: Adjust the tooltips, colors and formats.
And with this we conclude the chart of colored between lines!
You can see that both techniques have a similar level of difficulty.
Which technique do you prefer to use for your next line shading chart?
- Technique with Stack On. When the “marks” are accumulated on the axis.
- Technique with Stack Off. When the “marks” are not accumulated on the axis and the “marks” are superimposed on each other.
Next, I share a diagram to review how the “measures” of the area chart would look if they were adding one after another, in both techniques:
If you have any questions about the blog, do not hesitate to contact me on twitter (@rosariogaunag)