Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

delekdelek
New Contributor

Color Expression for daily price changes (actually, NOT)

Hi,

I am trying to change the text color of 'Price' values that have NOT changed from the previous day in my table.

Let's say a table looks like this:

Store #   12/12/17  12/11/17   12/10/17 and so on for 30 days

  22           2.99         2.99          2.89     and so on

  23           1.88          1.78         1.78

I want the 2.99 on 12/12 (since it didn't change from 12/11), and 1.78 on 12/11 (again, since it didn't change from 12/10) in red text.

I am trying something like this in the text color expression of 'avg(Price) measure':

If(avg(Price)=avg({<[UpdateDate.autoCalendar.Date]={"$(=UpdateDate.autoCalendar.Date-1)"}>} Price), red())

It doesn't work. Note that I have a autoCalendar in my script that takes my dates and provides me with Date, Month, Year, Quarters etc, and I have been using my date field as UpdateDate.autoCalendar.Date!


I have been using QlikSense for about 10 days now, but I think its pretty neat for many tasks!


Thanks in advance!

1 Solution

Accepted Solutions
delekdelek
New Contributor

Re: Color Expression for daily price changes (actually, NOT)

Hi,

My data was was being displayed in a pivot table. Following Anil's suggestion, I tried using the 'above()' method, but it was comparing to a previous row. I needed comparison with previous column, so using 'after()', I was able to accomplish what I wanted:

If(avg(Price)=After(avg(Price)), red()).

I couldn't have known to use after() hadn't Anil suggested above(). So thank you!

The second suggestion If(avg(Price)=avg({<[UpdateDate.autoCalendar.Date]={"=[UpdateDate.autoCalendar.Date] = Date(([UpdateDate.autoCalendar.Date]-1))"}>} Price), red()) didn't work though.

Thank you both for your suggestions!

4 Replies
YoussefBelloum
Esteemed Contributor

Re: Color Expression for daily price changes (actually, NOT)

Hi,

what is the structure of your table ?

Re: Color Expression for daily price changes (actually, NOT)

If you look previous Date, you could try this?

If(avg(Price)=Above(avg(Price)), red())

OR

If(avg(Price)=avg({<[UpdateDate.autoCalendar.Date]={"=[UpdateDate.autoCalendar.Date] = Date(([UpdateDate.autoCalendar.Date]-1))"}>} Price), red())

Life is so rich, and we need to respect to the life !!!
delekdelek
New Contributor

Re: Color Expression for daily price changes (actually, NOT)

Hi,

My data was was being displayed in a pivot table. Following Anil's suggestion, I tried using the 'above()' method, but it was comparing to a previous row. I needed comparison with previous column, so using 'after()', I was able to accomplish what I wanted:

If(avg(Price)=After(avg(Price)), red()).

I couldn't have known to use after() hadn't Anil suggested above(). So thank you!

The second suggestion If(avg(Price)=avg({<[UpdateDate.autoCalendar.Date]={"=[UpdateDate.autoCalendar.Date] = Date(([UpdateDate.autoCalendar.Date]-1))"}>} Price), red()) didn't work though.

Thank you both for your suggestions!

mato32188
Contributor III

Re: Color Expression for daily price changes (actually, NOT)

Hi Raj,

have you tried before(), after() functions? Pivot table allows usage of wide variety of functions ...

BR

Martin

Community Browser