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
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Ignore selection of a Interval() and FirstSortedvalue functions

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?

1 Solution

Accepted Solutions

Try this

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

and this

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

View solution in original post

9 Replies

Try this

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

and this

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

View solution in original post

Contributor III
Contributor III

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 "-"...

How about this

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

Contributor III
Contributor III

Neither...I just tried it.

This doesn't work with and without selections?

Contributor III
Contributor III

It shows nothing

Screenshot_1.png

and this worked?

INTERVAL(unsubscribe_date - subscribe_date, 'd')

Can you show an image of the above working?

Contributor III
Contributor III

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!

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