Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

massimofavaro
New 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

Re: Ignore selection of a Interval() and FirstSortedvalue functions

Try this

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

and this

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

9 Replies

Re: Ignore selection of a Interval() and FirstSortedvalue functions

Try this

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

and this

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

massimofavaro
New Contributor III

Re: Ignore selection of a Interval() and FirstSortedvalue functions

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

Re: Ignore selection of a Interval() and FirstSortedvalue functions

How about this

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

massimofavaro
New Contributor III

Re: Ignore selection of a Interval() and FirstSortedvalue functions

Neither...I just tried it.

Re: Ignore selection of a Interval() and FirstSortedvalue functions

This doesn't work with and without selections?

massimofavaro
New Contributor III

Re: Ignore selection of a Interval() and FirstSortedvalue functions

It shows nothing

Screenshot_1.png

Re: Ignore selection of a Interval() and FirstSortedvalue functions

and this worked?

INTERVAL(unsubscribe_date - subscribe_date, 'd')

Can you show an image of the above working?

massimofavaro
New Contributor III

Re: Ignore selection of a Interval() and FirstSortedvalue functions

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!

Re: Ignore selection of a Interval() and FirstSortedvalue functions

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

Community Browser