Conditional formatting comparing previous row values

Question & Answer
, ,
1

Hi Team
Requirement that I have is to do conditonal formatting by comparing previous row values.
for eg,
Monthly Sales data
Sales Month Amount$
Jan-23 2000
Feb-23 2500 (As sales is higher compared to previuos row, highlight in Green)
Mar-23 2500
Apr-23 1500 (As sales dropped compared to pervious row MAR-23, highlight in Red)
May-23 1500
Jun-23 1000 (As sales dropped compared to pervious row MAY-23, highlight in Red)

Any help/pointers will be helpful,thanks

2

Hi @Subrjaya2
i guess you need to create a calculated field with the condition and use that field to do the conditional formatting.
BR

1 Like
3

Hi,
I used lag function in a calculated field and got the previous row value adjacent to each row. but it would not allow me to subtract the values as one of them used a function column.

If I could minus the values, i will know if the sales increased or dropped and set a conditional formatting in Difference column. Any help or workaround that you can suggest please

lag(
sum({Amount}),
[Sales Month asc], 1 )

Sales Month Amount$ calculated field Difference
Jan-23 2000
Feb-23 2500 2000
Mar-23 2500 2500
Apr-23 1500 2500
May-23 1500 1500
Jun-23 1000 1500

4

hi @Subrjaya2,

can you try please with the below calc filed ?

sum({Amount}) - lag(sum({Amount}),[Sales Month asc], 1 )
5

Thank you, it helped.
Keeping that as calc_field1, now, one more requirement is, I need to subtract amount from calc field1 to find out if there has been an increase/decrease to do conditional formatting, but it is not allowing me to do so.

calc_field2:
amount - calc_field1

Any idea will be helpful. Thanks again.

2 Likes
6

Hello @Subrjaya2, it has been awhile since we have heard from you. Are you still having issues with the last question you asked, or have you been able to resolve the issue? If so, please provide the solution that worked for you or mark a response above as the solution.

Otherwise, if you still need assistance, please let me know and provide any information about tests you have run to make this work. If we do not hear back from you in 3 days, I will archive this topic. Thank you!

7

Hello @Subrjaya2, since we have not heard back from you, I will archive this topic. If you still need assistance with this issue, please post a new question in the community and link to this topic. That will ensure you are at the top of the priority list for a response from one of our QuickSight experts. Thank you!