Take Advantage of Card Widgets by filtering value with an expression
The Card Widget was designed to measure trends through key performance indicators (KPIs) like Value and Goal according to the documentation available here.
It is a good widget to get a global overview of data changes.
But maybe you just want to use the card widget to show a difference between two values. It doesn’t have to be necessarily a Goal value.
For example, I wanted to show the progression of the Sake brewery count number in each Japan’s prefecture between 2008 and 2012.
Let’s take a look at the data I had:
1st Column: Prefecture Name
2nd Column: Year
3rd Column: Count number of Sake Breweries
To be able to apply a filter based on a Datetime to a prefecture I had to repeat the prefecture information like above.
You can see the same Kanji (Chinese Character) repeated 5 times. This is the Nagano Prefecture (first column) and its count number of Sake breweries during 5 years.
To compute the progression, I had to get values of each prefectures for 2008 and 2012.
So if you have the same kind of values, here is how to get just a specific value using an Expression Column like this:
Let’s translate the columns to make it easier to understand and use the following columns:
Year_column as the 2nd column and Sake-brewery-count as the 3rd column.
So you could use the following Expression:
IF ( YEAR(‘year_column’) = ‘2008’ , ‘Sake-brewery-count’ , NULL)
We could translate this by “If the year is 2008, show me the sake-brewery-count column, otherwise set NULL value”.
The YEAR() function : Returns a numeric value representing the year part of the date resulting from the specified date expression.
You can see the complete Expression function list in the documentation here.
The output result of this expression is:
My expression column was added like above. The result is the 4th Column. As expected, the count number of Sake Breweries appears only when the date is 2008 and leaves a blank (as NULL) for the other years.
You can do the same for the other value (in my case the 2012) by adding a new expression with the new date instead of 2008. It will add a column per expression with only your filtered value.
Now, let’s assign data to the Card widget with these expressions like below:
The Actual Value doesn’t change from the original widget design. For this example, it’s 2012, because my data only goes until that year, but it would really be your actual value.
The change is for Target value. It becomes your “past value”. In this example, the oldest value available is 2008, because I wanted to show the difference between the oldest and the newest values.
Here is the output of my widget:
It shows me the percentage change of the count number of Sake Breweries for a selection of prefectures between 2008 and 2012.
So, you could reproduce this with any kind of data when you want to show a difference between two values.