Bar colouring for most recent + condition

Question & Answer
, , ,
1

Hello Team!

With the following data:

|Date|Value|

|2021-04-01|1|
|2021-05-01|9|
|2021-06-01|9|
|2021-07-01|3|

I was asked to do the following:

  1. find the most recent period
  2. for this period check if value is smaller than 9
  3. value smaller than nine is green, otherwise red
  4. Present in a form of a bar chart

I calculated it in the following way:

  1. Find last Date (Created artificial aggregate, IDK if needed?):
    max(Date, [{Artificial aggregate}])

  2. Check if date is last date and apply colours if needed:

ifelse(
max(Date)=max({BC - last date}),
ifelse(max(Value)>=9,‘red’,‘green’),‘empty’)

  1. It works like charm for a table - the test column is a source for conditional formatting and values colour accordingly:

    image
    image846×143 11.8 KB

  2. I can’t, however, use this calculation in colour group for the bar chart - custom aggregations are not allowed.

Could you advise me with a solution to this issue?

The dashboard:
Most recent period - bar chart colouring

2

Hello @Filo !

Could you try something like the following calculated field and apply it to your Group/Color By field?

ifelse(
Profit >= 1, 
"Good",
Profit <= 0, 
"Bad",
NULL
)

For “Good” and “Bad” you can use the metric that you are measuring for, just applying them as if they are string values. Essentially you are turning the conditions into groups for your data.

Then, change the color of the bars in the visual menu by clicking on the bar and assigning the color by group.

image
image1042×568 37.9 KB

Let me know if that helps!

3

Hello @duncan! Thanks for reaching out :slight_smile:

I am not sure if this is enough for this case, sadly. I can create a calculation that colours each bar charts depending on value, but the issue I’m facing is that it should be applied for the last period only. The way I see it, this requires table calculation/aggregation that is not allowed for grouping field. I was wondering if this is achievable in QS :slight_smile:

So,

  1. find last period
  2. colour last period depending on value
  3. (The rest of the bar charts can be coloured in gray, for example)

Below all bars coloured :slight_smile:
image

The state I am aiming at:

image
image697×880 6.53 KB

Thanks for help!

4

… is there a way to colour last bar only, for starters?

5

Hello @Filo !

Got it, I believe what you are describing is not supported yet, but looks like it is something that is on the Quicksight teams radar:

I will mark this a feature request for the Quicksight team!

1 Like