topic Re: cross table and aggregation in QlikView

cross table and aggregation

saumyashah90 — Tue, 08 Dec 2015 10:56:25 GMT

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

Re: cross table and aggregation

sunny_talwar — Tue, 08 Dec 2015 11:05:04 GMT

I don't see anything attached. Can you please reattach it?

Re: cross table and aggregation

effinty2112 — Tue, 08 Dec 2015 15:14:01 GMT

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

Re: cross table and aggregation

saumyashah90 — Wed, 09 Dec 2015 02:32:20 GMT

so sorry to not attach

PFA

Re: cross table and aggregation

saumyashah90 — Wed, 09 Dec 2015 04:07:59 GMT

This doesnt work.

This hits repeats for everymonth

Re: cross table and aggregation

PrashantSangle — Wed, 09 Dec 2015 04:15:34 GMT

Hi,

Can you tell us logic behind it.

Regards,

Re: cross table and aggregation

effinty2112 — Wed, 09 Dec 2015 09:04:16 GMT

Hi Saumya,

In your xls file each user has only one record so aggregating this across the user dimension is not telling us anything.

Re: cross table and aggregation

nagarjuna_kotha — Wed, 09 Dec 2015 09:27:21 GMT

Like this ??

PFA

Re: cross table and aggregation

nagarjuna_kotha — Wed, 09 Dec 2015 09:29:38 GMT

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