Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
MEBG93
Creator
Creator

set analysis when max(date, 2) not working when selecting a different date

Hello everyone,

I have an issue on a set analysis date selection, I want to select the penultimate date available. It only works without any filters applied, but it stops working when a different date is selected. The expression goes like:

=COUNT({< [DATE]={'$(=MAX(DATE,2))'}>}operation_id)

PD: it works perfectly (with and without filters) when looking for actual date (max(date))

 

Thanks!

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

How about this

Count({<[DATE] = {"$(=Max({<DATE = {[<$(=Max(DATE))]}>}DATE))"}>} operation_id)

View solution in original post

11 Replies
vunguyenq89
Creator III
Creator III

Hi,

Because =MAX(DATE,2) returns the second highest date, the expression only works if you select at least 2 days from the filter.

Anil_Babu_Samineni

Max(date,2) always picks second maximum date even filter/not filter. Could be this way?

=If(GetSelectedCount(DATE)>0, Count(operation_id), COUNT({< [DATE]={'$(=MAX(DATE,2))'}>}operation_id))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MEBG93
Creator
Creator
Author

Hey Anil, thanks for responding. Doesn't work because if shows the highest date. I need the second hightest date.

I know that when you select a date, for instance, 2019-11-10. And you calculate count({<[DATE]={"=$(=max(DATE, 2))"}>}operation_id) it results on a null date.

So I need to show/unhide the second highest date available, based on that selection.

 

Thanks

Anil_Babu_Samineni

Try this?

count({<[DATE]={"=$(=max({1}DATE, 2))"}>}operation_id)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

How about this

Count({<[DATE] = {"$(=Max({<DATE = {[<$(=Max(DATE))]}>}DATE))"}>} operation_id)

View solution in original post

MEBG93
Creator
Creator
Author

it works! Thanks Sunny.

MEBG93
Creator
Creator
Author

I'm curious. What's the logic behind that set analysis? 

sunny_talwar

The inner set analysis or the outer one?

MEBG93
Creator
Creator
Author

Both, but more interested on how the inner set analysis brings out the second highest date without calling the max(date, 2)