Conditional formatting based on variable

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 don’t know how to insert this variable in the formatting field of my chart dimension. How can I do this? Thanks in advance!

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:

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.

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

variable

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

I’ve 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 don’t 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:

Is it no longer Celonis’ intention to address this missing functionality?

Thank you,

Alex

1 Like

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

1 Like