Staring blind on creating the expression ... HELP!

[Anonymous]

I think I'm staring blind at this ... maybe someone can help me find the light ?

I have a table with sessions:

SESSION_ID, SERVICE_ID, ........ , INTERVAL
1, 123, .... , 1
2, 123, .... , 1
3, 124, .... , 28
4, 123, .... , 30
5, 125, .... , 1
6, 123, .... , 30
7, 126, .... , 30

I'm looking for something to show the number of SERVICE_ID's for a certain INTERVAL.

The count should be something with the DISTINCT SERVICE_ID and the max(SESSION_ID) ...

Result should be something like:

1 - 1x
28 - 1x
30 - 2x

I would like the INTERVAL to be a dimension ...

Can anybody help my on the way with the expression??

many thanks!!

Anita

[Oleg_Troyansky]

Anita,

I think you need to clarify to us and to yourself, what exactly are you after? For example:

A Fuchten wrote:I'm looking for something to show the number of SERVICE_ID's for a certain INTERVAL.

----------------------------

If INTERVAL is a Dimension, then COUNT(DISTINCT SERVICE_ID) would answer this question.

On the other side:

A Fuchten wrote:The count should be something with the DISTINCT SERVICE_ID and the max(SESSION_ID) ...

----------------------------

not sure what you mean here... What's the relation to the Session ID ?

A Fuchten wrote:
Result should be something like:
1 - 1x
28 - 1x
30 - 2x

------------------------------

I counted 2 distinct Sessions with Interval = 1. Am I counting wrongly?

please clarify your needs...

[johnw]

If I understood the question:

[Data]:
LOAD * INLINE [
SESSION_ID, SERVICE_ID, INTERVAL
1, 123, 1
2, 123, 1
3, 124, 28
4, 123, 30
5, 125, 1
6, 123, 30
7, 126, 30
];
LEFT JOIN ([Data])
LOAD *
,if(SERVICE_ID<>previous(SERVICE_ID),1) as Max?
RESIDENT [Data]
ORDER BY SERVICE_ID,SESSION_ID DESC
;

Dimension = INTERVAL
Expression = count({<Max?={1}>} distinct SERVICE_ID)

[johnw · Accepted solution]

Non-script approach if you want the max(SESSION_ID) to be responsive to selections:

Dimension = INTERVAL
Expression = count({<SESSION_ID={$(=concat(aggr(max(SESSION_ID),SERVICE_ID),','))}>} distinct SERVICE_ID)

Not sure about the performance, though, and it might crash if your data set is large enough. Might be some other approach that would work better.

[Anonymous]

Looks that this is what i'm looking for ...

Will try this out later today!!