In this blog, I want to share how to use a date parameter in calculated fields, reference lines and colour quadrants.
Hi everyone, my name is Bruna Guglielmi. I am from The Data School cohort 26 in Sydney, Australia.
In the first week, we learned about parameters and after playing around a bit, I immediately faced a challenge: My reference line, connected to my date parameter (for interactivity), was not working at all. I wanted to create a calculated field that would update my quadrant object colours about the date and value of my axis, like this:
So, both reference lines are connected to an interactive parameter. You can increase the sales value or move the date to the future or past, and the colour dots will be updated.
The problem I faced was: How to make an if-else statement with a parameter date 0.0
I had to remind myself (with my coach’s help, of course) that a parameter is nothing more than a number field. As you all can see, the current value has the whole date information (dd/mm/yyyy hh). So if my measure in the column is MONTH(ORDER DATE), and I compare this in a calculated field, that’s not going to help. It’s like comparing bananas with apples.
That’s why when using a parameter in an if-else statement, you need to add the DATETRUNC() function with the same date part you are using in ORDER DATE when comparing both. And also use the ATTR() to make all the comparisons aggregated at the same level.
So if you were having this problem, I hope this helps you solve it, and otherwise, if you were just curious, I hope this helped you learn something new. And if you want to check other of my blogs and videos please check my Subtack.
Thanks again.
(The data used in this post was the Sample Superstore from Tableau.)