# Bar colouring for most recent + condition

**URL:** <https://community.amazonquick.com/t/bar-colouring-for-most-recent-condition/26715>\
**Category:** Q&A\
**Tags:** formulas, bar-chart, feature-request, calculations, Business-Intelligence-Engineer, quick-sight\
**Created:** [March 1, 2024, 5:14pm UTC](https://community.amazonquick.com/t/bar-colouring-for-most-recent-condition/26715 "2024-03-01T17:14:44Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Filo](https://avatars.discourse-cdn.com/v4/letter/f/848f3c/32.png) [@Filo](https://community.amazonquick.com/u/Filo)\
**Post date:** [March 1, 2024, 5:14pm UTC](https://community.amazonquick.com/t/bar-colouring-for-most-recent-condition/26715/1 "2024-03-01T17:14:45Z")

</div>

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:  

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](https://community.amazonquick.com#Arena-dashboardId=92c42387-2a0b-4511-bb51-9ec8b3024237&authorId=CU-8060)

---

<div class="post-metadata">

**Author:** ![WLS-D](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/wls-d/32/36109_2.png) [@WLS-D](https://community.amazonquick.com/u/WLS-D)\
**Post date:** [March 1, 2024, 10:44pm UTC](https://community.amazonquick.com/t/bar-colouring-for-most-recent-condition/26715/2 "2024-03-01T22:44:48Z")

</div>

Hello @Filo !

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

```auto
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](https://d1wl4jav5dp96l.cloudfront.net/original/3X/b/b/bb4adde94e19e304610109139af0a715d5d2dcfd.png)

Let me know if that helps!

---

<div class="post-metadata">

**Author:** ![Filo](https://avatars.discourse-cdn.com/v4/letter/f/848f3c/32.png) [@Filo](https://community.amazonquick.com/u/Filo)\
**Post date:** [March 2, 2024, 11:10pm UTC](https://community.amazonquick.com/t/bar-colouring-for-most-recent-condition/26715/3 "2024-03-02T23:10:33Z")

</div>

Hello @WLS-D! Thanks for reaching out 🙂

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 🙂

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 🙂  
 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/6/6/668731b9888a67a665ebdd8813edb2684cae68de.png)

The state I am aiming at:

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/5/9/591de193b070c06a54b7637cfe6e9d9a784d22f0.png)

Thanks for help!

---

<div class="post-metadata">

**Author:** ![Filo](https://avatars.discourse-cdn.com/v4/letter/f/848f3c/32.png) [@Filo](https://community.amazonquick.com/u/Filo)\
**Post date:** [March 4, 2024, 2:05pm UTC](https://community.amazonquick.com/t/bar-colouring-for-most-recent-condition/26715/4 "2024-03-04T14:05:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![WLS-D](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/wls-d/32/36109_2.png) [@WLS-D](https://community.amazonquick.com/u/WLS-D)\
**Post date:** [March 4, 2024, 3:47pm UTC](https://community.amazonquick.com/t/bar-colouring-for-most-recent-condition/26715/5 "2024-03-04T15:47:18Z")

</div>

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:

> [@Conditional Formatting: Bar Graphs & Line Charts](https://community.amazonquick.com/t/conditional-formatting-bar-graphs-line-charts/2181):
>
> Is it possible to conditionally format bar graphs and line charts? I know the QuickSight documentation says it is only available for gauge charts, KPIs, Pivot tables & tables, but I am wondering if there is a workaround that anyone has discovered? If not, how do we have this raised as a feature request?! Would be great to have. I have a lot of customers that I work with who like to have their bar graphs and line graphs formatted based on RAG conditions.

> [@How to colour/group percentage difference using custom aggregation field](https://community.amazonquick.com/t/how-to-colour-group-percentage-difference-using-custom-aggregation-field/22723):
>
> I have a dataset which i am using for learning. It is fruit production for UK, France, and Spain, from 2014-2018. I am focusing on UK, on the fruit “Pears”. I have a chart which i want to show the percentage difference for each year in production of Pears. It looks like this: It is quite simple, but i want to colour anything above 0% green, and anything below 0% red. So it colours whether it was a positive or negative difference. The only problem is that i can’t use a calculated fie…

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

---

<div class="post-metadata">

**Author:** ![WLS-D](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/wls-d/32/36109_2.png) [@WLS-D](https://community.amazonquick.com/u/WLS-D)\
**Post date:** [March 5, 2024, 3:20pm UTC](https://community.amazonquick.com/t/bar-colouring-for-most-recent-condition/26715/6 "2024-03-05T15:20:17Z")

</div>