set analysis

[mambi]

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?

[swuehl]

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)

?

[giakoum]

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

[sunny_talwar]

Something like the attached? PFA

Best,

Sunny

[mambi]

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)

[giakoum]

see attached

post from swuehl and sunindia already gave you the solution

[sunny_talwar]

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)

[mambi]

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

[mambi]

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

[sunny_talwar]

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