Hi Saumya,
This would be easier with an xls file to work with and a definition of what comprises a session, a Hit or a User Selection?
Anyway you can do this with a pivot table if you create the aggregate fields Last Login, and Average Login in the script first and create a Month field too.
Data:
Load
USER,
Date(Date) as Date,
Duration,
Hits,
[User Selection],
Month(Date) as Month;
LOAD * INLINE [
USER, Date, Duration, Hits, User Selection
U1, 07/12/2015, 32.23, 1, 12
U2, 07/12/2015, 50.85, 3, 30
U3, 07/12/2015, 126.37, 4, 36
U4, 07/12/2015, 63.93, 2, 16
U5, 07/12/2015, 122.93, 10, 92
U6, 07/12/2015, 89.17, 2, 24
U7, 07/12/2015, 39.95, 3, 35
U8, 04/12/2015, 65.73, 4, 24
U9, 04/12/2015, 134.9, 2, 34
U10, 04/12/2015, 71.3, 2, 19
U11, 04/12/2015, 19.6, 8, 100
];
Aggregate:
LOAD
USER,
Date(Max(Date)) as LastLogin,
Round(Sum(Duration)/Sum(Hits)) as [Avg Login] //Replace with the correct definition if necessary
Resident Data
Group by USER;
Now you can make a pivot table like this. USER, LastLogin, Avg Login and Month are dimensions. The expression Sum(Hits) is used here for No of Logins, I don't know if that is the definition you are using:
| USER | LastLogin | Avg Login | Month | Dec |
|---|
| U1 | 07/12/2015 | 32 | | 1 |
| U2 | 07/12/2015 | 17 | | 3 |
| U3 | 07/12/2015 | 32 | | 4 |
| U4 | 07/12/2015 | 32 | | 2 |
| U5 | 07/12/2015 | 12 | | 10 |
| U6 | 07/12/2015 | 45 | | 2 |
| U7 | 07/12/2015 | 13 | | 3 |
| U8 | 04/12/2015 | 16 | | 4 |
| U9 | 04/12/2015 | 67 | | 2 |
| U10 | 04/12/2015 | 36 | | 2 |
| U11 | 04/12/2015 | 2 | | 8 |
