Accumulate Single Column Time Value

[knotty150]

Hi All

I'm struggling with calculating a daily/weekly/monthly time duration field based off a single date/time column. I've got a table which monitors website activity, recording every change of page within the website with fields like:

IP Address
URL
Date/Time Requested

I've managed to produce a calculation which calculates the duration an IP address spent on our website by doing something like:

CalculatedColumn1: =1.*[Date/Time Requested]
MaximumDateTime:=MAX([CalculatedColumn1])
MinimumDateTime:=MIN([CalculatedColumn1])
MaxMinDifference:=[MaximumDateTime]-[MinimumDateTime]
Duration:=24*[MaxMinDifference]*60

Which calculates the duration in minutes any given IP address spent on the site. What I'm struggling with now is essentially accumulating the sum of all these durations in a daily/weekly/monthly format.

Anybody able to help?

Many Thanks!

Rich

 

[scottsen]

You will really want a separate calendar table that you relate to your current fact table. Then any sort of measure (calculated field) with =SUM(FactTable[Duration]) will magically "work" against any time frame.

 

[knotty150]

Hi Scottsen how are you?

You'll have to forgive me; I'm still pretty new to PowerPivot and I can't really understand your response. I'm already using a separate calendar table which I'm linking to my facts table via date but I'm unsure how I can lever that in this issue? I could understand if "Duration" was a column held in my facts table, but it's actually a calculated measure I've created using the method explained in my previous post. Whilst this method works for an individual IP address, it doesn't work for calculating the duration on a day/week/month basis because it's just comparing a max to a min field, as a pose to accumulating the sum of the differences between the min/max fields across all IP addresses on 1 day.

If it we're the case the date/time field I'm trying to perform this calculation on was stored across 2 separate fields, I know I'd then be able to create a calculated column which I could then perform a sum on, but with there being only 1 field for date/time per record, I don't really know how I can go about accumulating the result.

Does that make sense?

Thanks in advance!

Rich

 

[scottsen]

How do you plan to handle the same IP address returning multiple times? (MAX minus MIN would cover "too much" time?)

 

[knotty150]

Morning Scottsen

Right now I'm using rows for Date then IP Address. I realise this is only really accurate for instances where an IP address visits the website once per day (doesn't really work if they visit in the morning, then return later in the evening), but the results I'm seeing from this look fairly plausible and I can't really think of anything I could do to eliminate multiple sessions per day per IP address other than maybe just filter them out?

Thanks!

Rich

 

[scottsen]

No RequestId or similiar? Is your data in SQL, or just a CSV file?

For your specific question, you have a [Duration] measure, and... you want to sum that up. Likely you are going to use SUMX for that.

=SUMX(Calendar, [Duration])

 

[knotty150]

Hi scottsen

Sorry for the late reply I thought I'd posted.

Unfortunately there isn't any RequestID or similar, all I've got to go by is IP address. All the data is in SQL.

I'm struggling with your proposed SUMX solution. All it seems to return is the greatest duration a single IP has achieved in one day, as apposed to summing the durations by IP address. I think it's probably down to the formula I'm using in my current "Duration" measure but I can't think of another method given the date/time information is held in a single column.

Sorry about this scottsen, you'll have to be patient with me!

Thanks again.

 

[scottsen]

Can you share a workbook on google drive, onedrive or dropbox?

 

[knotty150]

Hi Scottsen

https://onedrive.live.com/?cid=F9402ACBDF9DC6BE&id=F9402ACBDF9DC6BE!105

Please access sample workbook above.

As you can see, the formula works quite nicely for individual IP addresses but doesn't calculate correctly for an entire day. I'm sure you'll be able to see what's going on. Have a look, let me know if you need anything else.

Thanks for your help!

Rich

 

[scottsen]

I don't think I helped the cause when I said:

=SUMX(Calendar, [Duration])

This is closer to what I meant...
DailyTotal := SUMX(VALUES(MasterDates[Date]), [Duration])

That said, since your data is in SQL, I would consider just writing a view that summarizes the data the way you want.

select DateDiff(millisecond, Min(DateTime), Max(DateTime)) as RequestLengthMs, cast(DateTime as Date) as RequestDate, IPAddress, PageUrl
from TheTable
group by cast(DateTime as Date), IPAddress, PageUrl