Ignore selection of a Interval() and FirstSortedvalue functions

[Anonymous]

Hello everyone.

I am stuck with a problem.

I have a table with a subscription date, unsubscription date and username.

What I'd like to achieve is a measure that shows the user with the longest active subscription time.

This one should be the correct formula:

INTERVAL(unsubscribe_date - subscribe_date, 'd').

No problem so far.

What I'd want is this formula to ignore filters and selection: I read about set analysis but I don't understand how to use it in this case (if it has to be used in this case).

I also want to display the username of the user with the longest subscription, and it should be:

FirstSortedValue(username, INTERVAL(subscribe_date - unsubscribe_date))

Once again, I don't know how to make this formula ignore selections and filters.

Can anyone help me or give me a hint, please?

[sunny_talwar · Accepted solution]

Try this

Interval(Only({1} unsubscribe_date) - Only({1}subscribe_date), 'd')

and this

FirstSortedValue({1} username, Interval(subscribe_date - unsubscribe_date))

[Anonymous]

Thanks,

FirstSortedValue({1} username, Interval(subscribe_date - unsubscribe_date)) is working correctly.

The other one seems to be formulated correctly, since it says me "OK", but the result shown is "-"...

[sunny_talwar]

How about this

Interval(Only({1} unsubscribe_date - subscribe_date), 'd')

[Anonymous]

Neither...I just tried it.

[sunny_talwar]

This doesn't work with and without selections?

[Anonymous]

It shows nothing

[sunny_talwar]

and this worked?

INTERVAL(unsubscribe_date - subscribe_date, 'd')

Can you show an image of the above working?

[Anonymous]

Excuse me, my fault, I wanted that formula to be used in another one, and, obviously, that can't be simply used in a KPI object since it does not show just one value...  

I wanted to show the MAX() of that formula (with an IF statement), but I realized I can simply use this one:

MAX({1}IF(unsubscribe_date = '', , unsubscribe_date) - subscribe_date, 'd')

Sorry again, and thanks for that other formula!

[sunny_talwar]

No problem at all... I am glad you were able to figure this out