This is tricky one.
Follow the below instructions.
// Keep Null on datediff
DateDiff('second',lookup(min([Disconnected At]), -1),min([Connected At]))
// avg score by "day"
// format alignment
Sample_time_calc_SM_10.2.twbx 877.0 KB
That was a genius calculation, Shinichiro Murakami ! Thank You! I learned a lot from your reply.
Do you think you could help me with the visualization part of my question?
The goal was to represent the average per day. I update the table calculation fields to calculate the average per day (sheet1 copy). But I did not succeed creating a visualization (single bar, or any other) on sheet2
I'm attaching my the same sample file with the Table Calculation. I would highly appreciate if you could give me your input on this part.
SampleB.twbx 917.1 KB
Table calculation is VERY complicated.
Anyway, something like this?
Window_avg for ID
[Avg per ID]
and filter only one per ID by index filter.
SampleB_SM_10.2.twbx 924.2 KB
Thank You, Shinichiro Murakami
The bar chart is exactly what I was seeking to achieve.
However, the ideal was an average per day, not per ID (as per sheet1 copy).
I'm trying to make modification on your file but no sucess. Do you think it's because each day is attached to many variants (like; id and clientid)?
Can you help me?
Thank you so much in advance
I'm a bit confused.
The base calc of Datediff is based on ID or Client ID, I believe.
And what do you mean by Day average?
Day x ID / or Day x Client ID ?
Or on top of Day x ID / or Day x Client ID , Daily average calculation is needed?
Hi Shinichiro Murakami thank you so much for helping me with this.
Yes, DATEDIFF is based on ClientId. Basically, in case the clientID has a subsequent connection (2nd row) - then we calculate the datediff of the connection_at time and prior disconnected_at. If no subsequent row, there's no calculation - NULL.
The ID is necessary as the clientIDs are subgroup the ID itself. It's possible that same clientID shows up on another ID. For this reason, ID was kept in the table calculation. However, you are correct; the datediff is based on clientID.
Daily average, I meant as win_avg per day. As per image below,
I'm looking for a visualization that represents the table on the pic above.
Y-axis: Display win_avg
Do you think this bar chart is possible?
Thank You so much
I'm happy that I could help you.
Table calculation is tricky when it comes into multiple layer.