Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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...
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)
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.
Looks that this is what i'm looking for ...
Will try this out later today!!