Change colour of KPI (count of cases) compared to previous week/month

Question & Answer
, ,
1

Simple task i need to do, just not quite sure on how to tackle it (i assume using rules and parameters?)

I have a custom visual which i use to split a group into 3 different areas. The larger number in each one is “this months numbers”, and the smaller numbers below are “last months” numbers. What i want to do is change the colour of this months numbers to green if they are larger than last months numbers, or red if they are smaller. I think this can be done through rules, but I’ve never set them up or created rules before. The one exception is rejected, since we would want a lower rejected number which would ideally increase OIP (accepted cases).

In short, how can i change the colour of the larger text to green/red (higher / lower number) based on the smaller text? These are the same fields, just different date ranges

image
image1087×523 77.1 KB

2

Hi @HarveyB-B
did you check the “conditional formatting”?

grafik

Maybe you need to create a new field where you calculate the higher/lower and use that field as “based on”

BR

3

So create a calculated field which gets the count of data from last month, and then compare the current KPI to this field (green if higher, red if lower). Is this possible to filter data within a calculated field though?

4

The problem is i need to put a field holding last months data in “Value”, rather than a number i enter. Is there another way to compare the current months data to last months data? I have seen a couple things where they use other filters but i do not understand the process of doing it.

For example - Comparison with previous month while displaying all data for a month on Quicksight

Although it does not change the colour, i have a clear comparison of data from two different months

5

So using this conditional formatting, can i do it based on data from a time range, or do i need to have a separate calculated which filters the data on the time range?

Reasoning for this is that the two fields displayed are the same, just one is based on last month, whereas the other is this months.

6

Hello @HarveyB-B and @ErikG !

@HarveyB-B were you able to find a solution or workaround on this, or are you still facing this issue?

You could use conditional formatting but would need to create conditional statement in a calculated field that you could base the conditional formatting for your target field on. You could try the following example:

ifelse(
{this month values} - {last month value} >= 0, 1, 
{this month value} - {last month value} <= 0, 2,
0
)

You then use that for the conditional formatting of the values. It can be tricky but it mostly comes down to your dataset.

To make this example work you would need to create a calculated field based that would determine if a value is from this month or the previous. You could do that a few way, but it depends on your data. One way I have done it is by creating relative data parameters set to the previous month so ${StartLastMonth} and ${EndLastMonth}. Then create a calculated field to determine if your value field has an associated timestamp between those dates.