Where in Qlik Sense

[QlikQlaker]

I'm trying to highlight a field based on a condition.  there is a date and then a value so like, 15 feb value 3, 25 feb value 5, 01 mar val 3, and then predicted values so 01 may 10, 8 aug 15.  in the data set, there is a column that annotates the current value, but if the dates are the same, it doesn't show the repeating values (that's fine).  the problem is that if the date that is the current is not the latest date, then it doesn't highlight it.  

I don't want to filter the data on the load (because i need that in other places), I want to be able to compare the dates so that if the date value shown = date where current = 1, then highlight it.

i was thinking a where clause would be perfect for this, but i can't see a way to write the where clause in the qlik sense part of the app.

=if(ReportDate = ReportDate Where Current = 1, rgb(numbers))

Help?

[JuanGerardo]

Hi @QlikQlaker, maybe you are looking for something like this?

=if(ReportDate = Max({<Current = 1>} ReportDate), rgb(numbers))

Set analysis can work as a where clause.

JG

[QlikQlaker]

I was extra hopeful!  But it didn't quite work as planned .. 😞 

So there's also one more field, which is object, and each object has it own set of reportdate and current markings... i think that is the reason it didn't work... 

 This is one set of data, and it has it's own version of current... but there are hundreds of these that have this data, so is it possible to tie it to the object?

[JuanGerardo]

I have noticed I made a typo, so the correct expression should be:

=if(ReportDate = Max({<Current = {1}>} ReportDate), rgb(numbers))

Based on that data sample, which is the desired output?

JG

[QlikQlaker]

The desired output is that the latest date is highlighted.  I made a change to the data that is brought in so that data sets that are condensed down by qlik are easier to manage - but now I have a new problem... and i think this is what is driving the lack of highlighting in general.... i went and did a layered if statement so that if it were ANY of the only allotted values, that it would be highlighted.  If I include the column that has the value, it will add an extra row and highlight both columns. 

I think the issue is that the it is more than 1 value...

=if(Current=1, rgb(255,255,119), if(Current = 2, rgb(255,255,119), if(Current = 0,rgb(255,255,119)))) 

Is the statement that I use to color (i would take out the one that does the rest but was testing)

2/1/2021 : Current = 1 AND 2/1/2021 Current = 2

Do you know how I would address this?

[JuanGerardo]

Then I think you want to highlight the max report date for records where C has its maximum value (4 in your example):

=if(ReportDate = Max({<C = {$(=Max({1} C))}>} ReportDate), rgb(numbers))

JG

[QlikQlaker · Accepted solution]

So I ended up going the other way... since I couldn't get Qlik to identify the column I wanted highlighted, I highlighted everything i knew I didn't want with white, and then did an else to capture the column i couldn't target...

=if(Current = 0,rgb(255,255,255),rgb(255,255,119))

Thanks to all for the help!

[QlikQlaker]

I did try this btw - and it still didn't highlight the right field haha