Set Analysis related Query

[#1]

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
100000		14	16
100002	10.109.39.95	16	16
100003	10.109.39.245	16	16

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
11	10.109.39.108	100000
12	10.109.39.108	100000
13	10.109.39.108	100000
14	10.109.39.108	100000
11	10.109.39.95	100002
12	10.109.39.95	100002
13	10.109.39.95	100002
16	10.109.39.95	100002
11	10.109.39.233	100003
11	10.109.39.245	100003
12	10.109.39.233	100003
12	10.109.39.245	100003
13	10.109.39.233	100003
13	10.109.39.245	100003
14	10.109.39.233	100003
14	10.109.39.245	100003
16	10.109.39.245	100003

[anbu1984]

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)

[#3]

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.

[#4]

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)

[#6]

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

[tresesco · Accepted solution]

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 =' '

[#9]

Thanks to Everyone for such active response and support