Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
?
dont understand why Jan is not 1 and Mar is not zero...
Something like the attached? PFA
Best,
Sunny
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)
see attached
post from swuehl and sunindia already gave you the solution
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)
Sorry, but it's not what i expected because in JAN i should get 0 not 1...
it's seems to be correct but why i can't just use set analysis intersection ?
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