Recipies
Copy axis order
There is a use case when one chart's axis must be ordered by another chart's axis. It's especially common when dealing with monthly data.
Let's say we are working with December's data and this is our query:
SELECT (2, 1, rows=[4], columns=[2]) as dec;
SELECT (1, 1, rows=[7:-5], columns=[1, dec])
-> SORT_BY ([1], axis=1, asc=False)
-> FILTER (x == "NA", axis=0)
-> PIN (["Other"], axis=0, "top")
-> TRIM ([:15], axis=0)
>> (1, 1)
This is what we can do:
SELECT (2, 1, rows=[4], columns=[2]) as dec;
SELECT (1, 1, rows=[7:-5], columns=[1, dec])
-> SORT_BY ([1], axis=1, asc=False)
-> FILTER (x == "NA", axis=0)
-> PIN (["Other"], axis=0, "top")
-> TRIM ([:15], axis=0) as rows;
SELECT (1, 1, rows=[4], columns=[5]) as sept;
SELECT (1, 1, rows=[rows], columns=[1, sept]) >> (1, 1)
rows variable;
3) we used rows variable with September's data. And it does the trick because SELECT controls not only what to select but also how to select (in what order).
Significance test
Given a data set:
| a | b | c | d | e | f | g |
| eg | e | eg | ||||
| 70 | 66 | 65 | 67 | 61 | 68 | 61 |
| 100 | 200 | 150 | 250 | 70 | 120 | 200 |
a previous-point significance test could be applied as follows:
-
Write a condition for base values:
SELECT (1, 1, [4], [2:]) > 29 as base;
True True True True True True -
Write a condition that defines whether a data point is significantly different:
SELECT (1, 1, [1], [2:]) in SELECT (1, 1, [2], [:-1]) as diff;
False False False False False True -
Now we apply logical AND to the conditions 1 and 2. Which means both conditions must be met at the same time:
base & diff as cond1;
False False False False False True -
Write a condition that defines if the previous data point is greater than the current one:
SELECT (1, 1, [3], [2:]) > SELECT (1, 1, [3], [:-1]) as cond2;
True True False True False True -
Select the data points that may need significance test markers, then apply
SIG_TESTwith the conditions that we wrote earlier:
SELECT (1, 1, [3], [2:]) -> SIG_TEST (cond1, cond2) >> (1, 1)
66 65 67 61 68 61↑
In this case we cannot write conditions for data points in the first column. So you may need to use JOIN to include that column in the end.
Here is a slightly modified full version:
SELECT(1, 1, [4], [2:]) > 29 & SELECT(1, 1, [1], [2:]) in SELECT(1, 1, [2], [:-1]) as c1;
SELECT(1, 1, [3], [2:]) > SELECT(1, 1, [3], [:-1]) as c2;
JOIN(
SELECT(1, 1, [3], [1]),
SELECT(1, 1, [3], [2:]) -> SIG_TEST(c1, c2),
axis=0
) >> (1, 1)
Displaying % or Value Change Between Different Waves
There might be cases where you want to format the result so that a "+" sign appears only for positive values when comparing data from different selected queries.
| Column 1 | Column 2 | Difference | |
|---|---|---|---|
| Strongly Disagree | 36% | 40% | -4 |
| Tend to Disagree | 20% | 13% | +7 |
| Neither Agree or Disagree | 3% | 3% | 0 |
| Tend to Agree | 5% | 13% | -8 |
| Strongly Agree | 36% | 31% | 5 |
Suppose we want to compare the responses for "Strongly disagree" between the two groups, "Column 1" and "Column 2".
| Column 1 | Column 2 | Difference | |
|---|---|---|---|
| Strongly Disagree | 36% | 40% |
Take your first seleted data that you want to compare—in this case it is:
SELECT (1, "Table 11", ["Strongly disagree"], ["Column 1"])
This would grab the value
36%
And subtract it from your second selected date you are comparing it to—in this case it is:
SELECT (1, "Table 11", ["Strongly disagree"], ["Column 2"])
This would grab the value:
40%
It should look like this so far:
SELECT (1, "Table 11", ["Strongly disagree"], ["Column 1"]) - SELECT (1, "Table 11", ["Strongly disagree"], ["Column 2"])
Mathematially, this would look like:
0.36 -0.40 -> -0.04
Right now, this will display the difference between the two columns, but it won't display a + sign for positive values. To do this, we'll need to apply a FORMAT() transformation...
-> FORMAT( suffix = "+")
However, this will now add a + sign in front of every value. For example, +-0.4, so we will implement the IIF() function and set a condition so only positive values have a +. It should look something like this:
IIF (
(SELECT (1, "Table 11", ["Strongly disagree"], ["Column 1"]) - SELECT (1, "Table 11", ["Strongly disagree"], ["Column 2"])) < 0,
(SELECT (1, "Table 11", ["Strongly disagree"], ["Column 1"]) - SELECT (1, "Table 11", ["Strongly disagree"], ["Column 2"]))
-> ROUND(1) -> FORMAT (suffix="pts"),
(SELECT (1, "Table 11", ["Strongly Disagree"], ["Column 1"]) - SELECT (1, "Table 11", ["Strongly disagree"], ["Column 2"]))*100 -> ROUND(1) -> FORMAT(suffix="+")
) >> (1,4)
(SELECT(1, “Table 11”, [“Strongly disagree”}, [“Column 1”])- SELECT(1, “Table 11”, [“Strongly disagree”], [“Column 2”]))
Your condition is:
< 0
Your value to select when the condition is TRUE is:
(SELECT (1, “Table 11”, [“Strongly disagree”], [“Column 1”]) - SELECT (1, “Table 11”, [“Strongly disagree”], [“Column 2”]))
Note: The data is already present to print a
-if the output is a negative number, so adding a prefix is not necessary.
Your Value to select when the condition is FALSE is:
(SELECT (1, “Table 11”, [“Strongly disagree”], [“Column 1”]) - SELECT (1, “Table 11”, [“Strongly disagree”], [“Column 2”]))
Here is what we did: 1) We grabbed the data points we are looking to compare with each other; 2) Set our condition, output if TRUE, and output if FALSE with the IIF() function; 3) Ensured that if the condition was met, the output would be unchanged. Ensured that if the condition was not met, the output would format the value to have the prefix +.
Because 0.36-0.40=-0.04, and -0.04 is less than zero (our condition), the program will look at what to print if the condition is not met. In this case the query will print -4. If we were looking at Column 2 to Column 1, we would have 0.40-0.36=0.04, where 0.4 is greater than zero, and the program would print 4.
| Column 1 | Column 2 | Difference | |
|---|---|---|---|
| Strongly Disagree | 36% | 40% | -4 |
Sorting a list of names by their values
In some cases, you may want to sort a list of names based on their associated values. For example, identifying the top-performing items in a dataset.
Start by selecting the query that contains both the names and the values you want to sort:
Select (1, "Table 11", rows=[3, 5], columns=[2:7])
In this selection:
rows=[3, 5]--> Looks at Row 3, the names of the values, and Row 5, the values we are sorting
This is the dataset we are pulling:
This is how the data we pulled looks: | Column 1 | Column 2 | Column 3 | Column 4 | Column 5 | Column 6 | Column 7 | |:--------:|:--------:|:--------:|:--------:|:--------:|:--------:|:--------:| | 36% | 40% | 32% | 30% | 29% | 32% | 30% |
Taking this dataset, suppose we want to map the 5 highest rows in the "Strongly disagree" group. To do this, we will use the SORT_BY transformation:
-> SORT_BY ([2], "columns", asc=FALSE, na_position="last")
Where:
[2]-> refers to the values column"columns"-> sorts the dataset by columnsasc=FALSE-> sorts in descending order (highest to lowest)na_position="last"-> places non-numeric values at the end
At this stage, your data is ordered from highest to lowest values.
This is how the soted data looks like now: | Column 2 | Column 1 | Column 3 | Column 6 | Column 7 | Column 4 | Column 5 | |:--------:|:--------:|:--------:|:--------:|:--------:|:--------:|:--------:| | 40% | 36% | 32% | 32% | 30% | 30% | 29% |
To focus on the highest values, we will use TRIM() to keep the top 5 values in the dataset
-> TRIM ( [1:5], axis="columns")
If you only need the names of the top results, trim the columns:
-> TRIM ([1], "rows")
We should get this:
SELECT (1, "Table 11", rows=[3,5], columns=[2:7]) -> SORT_BY ([2], "columns", asc=FALSE, na_position="last") -> TRIM ([1:5], axis="columns") -> TRIM ([1], axis="rows") >> (1,1)
Here is what we did: 1) Selected the dataset containing names and values; 2) Sorted the data in descending order based on values; 3) Kept the top 5 enterie; 4) Extracted only the names of those top values.
This approach ensures you can quickly identify and display the highest-ranking items while keeping the output clean and focused.