Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis related Query

Dear Team,

i have Date, Session IP fields in my straight table. my requirement is to get session wise IP of the max Date.

below is the output table

SESSIONID Max Date IP MAX(DATE) =16
16 16
1000001416
10000210.109.39.951616
10000310.109.39.2451616

Issue is when i use Max(DATE) statement into Exprerssion its return the max Date, i have used the set analysis to get the max Date IP. the Set analysis is as below

=ONLY( {<DATE={'$(=MAX(DATE))'}>} IP)

but the max(date) returning me 16 DATE only. so those have max Date 16 that is working file but for those who have 14 max date they have problem.

below is the sample table also.

DATE IP SESSIONID
1110.109.39.108100000
1210.109.39.108100000
1310.109.39.108100000
1410.109.39.108100000
1110.109.39.95100002
1210.109.39.95100002
1310.109.39.95100002
1610.109.39.95100002
1110.109.39.233100003
1110.109.39.245100003
1210.109.39.233100003
1210.109.39.245100003
1310.109.39.233100003
1310.109.39.245100003
1410.109.39.233100003
1410.109.39.245100003
1610.109.39.245100003
1 Solution

Accepted Solutions
tresesco
MVP
MVP

Try:

=FirstSortedValue(Distinct IP,-DATE)          // Ignore the warning in the syntax when you try. It works fine.

View solution in original post

8 Replies
anbu1984
Master III
Master III

Set analysis works at table level and not at row level. So in this expr =ONLY( {<DATE={'$(=MAX(DATE))'}>} IP)  Max(DATE) always return 16, so you got result for sessionIds where your max(DATE) is 16 and for remaining sessionIds null value

Try this

=FirstSortedValue(IP,-DATE)

Not applicable
Author

This is only working only when Date Session and IP is not repeating. i just shared the sample and selected field only, i have more fields like DateTime etc. so when the same date IP repeat this not working.

Not applicable
Author

Apart from this my Question is When i put Max(date) into expression its return me the max(date) of that particular Deminsion only.

So how can i write Max(date) function into set analysis so that i can get the same result set.

rubenmarin

Hi Jitendra, if you have a table with SESSIONID as dimension, you can set this expression:

Aggr(If(DATE=Max(TOTAL <SESSIONID> DATE), IP), SESSIONID, DATE)

Not applicable
Author

this is failing when the same max date have more than one IP .

tresesco
MVP
MVP

Try:

=FirstSortedValue(Distinct IP,-DATE)          // Ignore the warning in the syntax when you try. It works fine.

rubenmarin

Hi Jitendra, if can be more than one IP by sessionid in the same date, the IP should be a dimension, so it's possible to give more than one value for each SESSIONID.

You can set SESSIONID as first dimension and second dimension can be:

=Aggr(If(DATE=Max(TOTAL <SESSIONID> DATE), IP), SESSIONID, DATE, IP)

Check supress null values in this dimension.

You'll need to create an expression, for testing purposes you can create an expression like =' '

Not applicable
Author

Thanks to Everyone for such active response and support