Hello all, I have a dropdown for choosing a time dimension (day, week, year etc.). My charts change with changing this dimension so I can see my data on a more or less aggregated base. The only thing I struggle with is the conditional formatting of the dimension. I tried with setting a second variable as the target formatting but dont know how to insert this variable in the formatting field of my chart dimension. How can I do this? Thanks in advance!
Question
Conditional formatting based on variable
Hi @MIA92,
welcome to our community!
You made it really far and almost did everything that is needed to solve the issue yourself! I assume you struggle on how to set up the threshold that it works as you want to.
First you have to define the variables describing the borders where the colors should switch. I will come to how they are filled in the second step. As an example the variables and the threshold could look like this:
image704520 18.9 KB
After setting up the threshold and variables we come to how to fill the variables:
As you have written you have already created a button dropdown and enabled it to switch between Years, Month and so on. What you have to do is almost the same procedure. In the following I provide an example on how to do it for one item. Basically, you have to add the new variables as it is displayed on the screenshot. The other items (e. g. month, week) are done the same way.
image403810 21.1 KB
The idea is that the values/borders for the threshold are adjusted when you switch between the dropdown entries (e. g. year, month).
Hopefully, that will solve your issue! Otherwise, please do not hesitate to reach out to us again.
Best,
Justin
Hello Justin,
sorry, I should have uploaded a example.
My Variable is called Haefigkeitsmassstab_Anzeige and can be .f for a rounded number or .2% for percentage (I want to switch between relative and absolute frequency).
If I put this variable in the formatting box, I always get percentage as a result.
Kr, Mara
variable287475 4.61 KB
Hi @MIA92,
as far as I understand the issue is not about how to set up the threshold, it is about how to format the numbers correctly right?
Unfortunately, you can not apply a variable in the formatting formula. However, you can possibly use a workaround. You could format both as the same format and adjust your KPIs. In this you would have to multiply your KPI by 100 so you get percentages. Then you could apply a variable to define in the unit field. In there you can use variables normally.
Hopefully, that will guide you to find a solution.
Best,
Justin
Hello Justin,
I tried to reproduce your suggestion by using
<%= Haeufigkeitsmassstab %>(CASE WHEN <%= Haeufigkeitsmassstab %> = 'AVG' THEN
case when "table_csv"."attribute" = 'x' then 100.0 else 0.0 end
ELSE case when "table_csv"."attribute" = 'x' then 1.0 else 0.0 end
END)and
<%= Haeufigkeitsmassstab %>(CASE WHEN VAR(<%= Haeufigkeitsmassstab %>) = 'AVG' THEN
case when "table_csv"."attribute" = 'x' then 100.0 else 0.0 end
ELSE case when "table_csv"."attribute" = 'x' then 1.0 else 0.0 end
END)but both threw an error. <%= Haeufigkeitsmassstab %> can be AVG (relative) or SUM (absolute).
And a second question: How can I add the % for <%= Haeufigkeitsmassstab %> AVG?
Kr, Mara
Ive solved it. I need to write <%= Haeufigkeitsmassstab %> = AVG.
I was able to use the % sign in the unit field by a variable <%= Haeufigkeitsmassstab %> = % but like to set this to an empty string for absolute view as I dont need comas etc. How is that possible?
Hi Mara,
Great to hear that you solved the issue yourself and that you share your knowledge with all of us! As variables usually represent a text replacement, it follows from the definition only that the variable will be replaced by AVG. In your case you want to check if the STRING AVG = AVG. Therefore you need the corresponding quotation marks.
Best regards
Justin
Hi Justin,
Back in July, Calandra addressed the issue of formats not accepting variables and said she would bring the issue to the Delivery team:
Dynamic Formatting in OLAP table Analysis
Hi,
I have a table that can either show Volumes or Ratios of Sales Orders fulfilling a particular criteria. I have a drop-down which allows the user to select Volume or Ratio and the numbers in the table update accordingly.
The issue is that when I flip to Ratio, I am not able to dynamically format the numbers as a %. Is there any way to specify the number format within the function editor?
Is it no longer Celonis intention to address this missing functionality?
Thank you,
Alex
Hi Alex,
unfortunately, I do not have an update on that matter. Our Product Management is prioritizing all Feature Requests. Therefore, I can not tell you a date when this feature will be in place. However, I will raise that Feature Request again and as soon as I have an update on it, I will post it in this topic.
Best regards,
Justin
I currently have the same issue. Replying here to maybe get an update once it was added.
Conditional formatting would be such a blessing.
I also have many analysises with a day/week/month dropdown filter, but the format for the date of all three options is: dd-mm-yyyy. Quite annoying to look at. Especially for Month and Week.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.