Conditional formatting based on Comparison of 2 Calculated Fields

Question & Answer
, , , ,
1

Hi
want to have a conditional formatting based on the comparison of
dynamic values of a calculated measures (like in Power bi).
I mean, we can compare 2 measure & based on that we can give color coding.

like this

ifelse(

{Yesterday Scrap Value} < {Yesterday Scrap Value Target}, ‘Red Color’

{Yesterday Scrap Value} >= {Yesterday Scrap Value Target} AND {Yesterday Scrap Value} <={Yesterday Scrap Red Value}, Yellow Color

{Yesterday Scrap Value} > {Yesterday Scrap Red Value}, Green Color

)

The values of the above 2 Calculated Fields changes on daily basis. ( We can achieve this on Static Values but we can’t rely on static values )

I have achieved using static values given to a Calculated filed but i want to compare 2 Calculated Fields

Static Scenario:

image
image562×726 41.4 KB

Dynamic Scenario? Achievable in QS?

1 Like
2

Hi @Venkat.Metadata ,

Instead of encoding the colour name, we can encode the same condition with a value , lets say 1, 0 or -1 and then you use the same field to achieve conditional formatting.

e.g. -
ifelse({Yesterday Scrap Value} < {Yesterday Scrap Value Target}, -1,
{Yesterday Scrap Value} >= {Yesterday Scrap Value Target} AND {Yesterday Scrap Value} <={Yesterday Scrap Red Value}, 0,
{Yesterday Scrap Value} > {Yesterday Scrap Red Value}, 1)

Thanks,
Prantika

3

@prantika_sinha , the above formula is not working

Yesterday Scrap Cost = coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Value_decimal}, NULL), 0)

Yesterday Scrap Cost Target = coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Target}, NULL), 0 )

Yesterday Scrap Red Value = coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Red}, NULL), 0)

OR the below formula is not working

ifelse (
coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Value_decimal}, NULL), 0) <
coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Target}, NULL), 0 ), -1,
coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Value_decimal}, NULL), 0) >
coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Target}, NULL), 0 ) AND
coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Value_decimal}, NULL),0) <=
coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Red}, NULL), 0), 0,
coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Value_decimal}, NULL), 0) >
coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Red}, NULL), 0), 1
)

ifelse(
{Yesterday Scrap Value} < {Yesterday Scrap Value Target}, -1,
ifelse ( {Yesterday Scrap Value} >= {Yesterday Scrap Value Target} AND {Yesterday Scrap Value} =< {Yesterday Scrap Red Value}, 0,
ifelse( {Yesterday Scrap Value} > {Yesterday Scrap Red Value}, -1, null)
)
)

image
image982×110 4.04 KB

none formula is not working

4

Hi @Venkat.Metadata

Could you please try the following calculated field and apply the conditional formatting.

Example:

ifelse(
 {AttendanceOver30Days} < {AttendanceTarget30Days},      1,  // red
 {AttendanceOver30Days} <= {AttendanceWarnThreshold},    2,  // yellow
                                                           3   // green
)

Apply color formatting:

  1. Select your gauge visual → Format visual.

  2. Expand Conditional formatting → Arc → Add rule

  3. Set Format field based on → PerfCode and Aggregation → Custom (no aggregation)

    • Code = 1 → Red
    • Code = 2 → Yellow
    • Code = 3 → Green

  4. Click Apply

The above calculated field is itself driven by your two measures, the gauge arc will recolor automatically as those values change.

5

Hi @Venkat.Metadata

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

6

I am not able to achieve with your information. Pls give more info.

7

Hi @Venkat.Metadata

Sorry, I’m out of ideas. I would recommend filing a case with AWS Support where we can dive into the details so that we can help you further. Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf. Hope this helps!