topic Re: set analysis in QlikView

set analysis

mambi — Wed, 03 Jun 2015 13:28:03 GMT

Hello everyone ,

here's my problem : from the table below i want to have a straight table with months as dimension then calculate with set analysis the number of lost users per month.

USERID	MONTH-IN	MONTH-OUT
1	JAN
2	JAN	FEV
3	FEV	FEV
4	FEV	MAR
5	MAR
6	MAR
7	MAR

for this example i should get :

JAN	0
FEV	2
MAR	1

any idea?

Re: set analysis

swuehl — Wed, 03 Jun 2015 13:31:18 GMT

Set analysis will not work in the context of a dimension line.

What about a chart with dimension MONTH-OUT and an expression

=count(USERID)

Re: set analysis

giakoum — Wed, 03 Jun 2015 13:32:50 GMT

dont understand why Jan is not 1 and Mar is not zero...

Re: set analysis

sunny_talwar — Wed, 03 Jun 2015 13:34:27 GMT

Something like the attached? PFA

Best,

Sunny

Re: set analysis

mambi — Wed, 03 Jun 2015 13:53:39 GMT

sorry, a little update MONTH-OUT = the month in witch a user had left :

so my final table should look like this :

	iN	LOST
JAN	2	0
FEV	2	2
MAR	3	1

0 in JAN because no user had left in this month (JAN)

2 : 2 users had left in FEV (user2 and 3)

1 : one user had left in MAR (user 4)

Re: set analysis

giakoum — Wed, 03 Jun 2015 13:57:05 GMT

see attached

post from swuehl and sunindia already gave you the solution

Re: set analysis

sunny_talwar — Wed, 03 Jun 2015 14:00:55 GMT

Script:

Table:

LOAD * Inline [

USERID, MONTH-IN, MONTH-OUT

1, JAN,

2, JAN, FEV

3, FEV, FEV

4, FEV, MAR

5, MAR

6, MAR

7, MAR

];

Table1:

LOAD USERID,

[MONTH-IN] as MONTH,

'In' as [In vs Out]

Resident Table

Where Len(Trim([MONTH-IN])) <> 0;

Concatenate (Table1)

LOAD USERID,

[MONTH-OUT] as MONTH,

'Out' as [In vs Out]

Resident Table

Where Len(Trim([MONTH-OUT])) <> 0;

DROP Table Table;

Dimension: MONTH

Expressions:

1) =Count({<[In vs Out] = {'In'}>}USERID)

2) =Count({<[In vs Out] = {'Out'}>}USERID)

Re: set analysis

mambi — Wed, 03 Jun 2015 14:05:07 GMT

Sorry, but it's not what i expected because in JAN i should get 0 not 1...

Re: set analysis

mambi — Wed, 03 Jun 2015 14:08:36 GMT

it's seems to be correct but why i can't just use set analysis intersection ?

Re: set analysis

sunny_talwar — Wed, 03 Jun 2015 14:21:47 GMT

Problem is not the expression, but the dimension. You somehow need to create a common dimension and with my limited knowledge, I don't know another way of doing this. But I am sure some of the experts here might be able to help you.

Best,

Sunny

Re: set analysis

mambi — Wed, 03 Jun 2015 14:28:40 GMT

Thanks a lot for your help Sir.