Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a excel.PFA,
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.90 | 2 | 34 |
U10 | 04/12/2015 | 71.30 | 2 | 19 |
U11 | 04/12/2015 | 19.60 | 8 | 100 |
i need output as
User | Last Login | Average login per session (mins) | No. of times login | |||||||||||
Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | |||
U1 | 03/12/2015 | 1 | 4 | 2 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 2 | 3 |
U2 | 03/12/2015 | 2 | 1 | 2 | 0 | 0 | 0 | 3 | 0 | 1 | 0 | 1 | 2 | 3 |
U3 | 02/12/2015 | 5 | 1 | 2 | 0 | 0 | 2 | 0 | 3 | 1 | 0 | 1 | 2 | 3 |
U4 | 01/12/2015 | 6 | 1 | 2 | 0 | 0 | 2 | 0 | 0 | 1 | 0 | 1 | 2 | 3 |
U5 | 01/12/2015 | 7 | 3 | 2 | 3 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 2 | 3 |
I don't see anything attached. Can you please reattach it?
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 |
so sorry to not attach
PFA
This doesnt work.
This hits repeats for everymonth
Hi,
Can you tell us logic behind it.
Regards,
Hi Saumya,
In your xls file each user has only one record so aggregating this across the user dimension is not telling us anything.
Like this ??
PFA
i'm not sure that above is correct.
Explain clearly about your question and logic of expression. thn only we can provide better result
-Nagarjuna