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

[MEBG93]

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!

[vunguyenq89]

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))

[MEBG93]

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)

[sunny_talwar · Accepted solution]

How about this

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

[MEBG93]

it works! Thanks Sunny.

[MEBG93]

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

[sunny_talwar]

The inner set analysis or the outer one?

[MEBG93]

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