How to catch a list of new/different users between months in Spotfire

[Hesloan Viana]

I have a list for every month of active users on my system and i need to see, for every month, the recurrency users, the new users and users lost by month.

For example: This is my users for january:

 

And this is my users for february:

 

I need to have an column that shows me, for february: 2 new users (F and G), 3 lost users (C, D and E) and 2 "normal" users (A and B).

In my data i have an calculated column that shows me that list of recurrency by every month, but how i can see the changes between 2 months like in my example (new users, lost users and "normal" users).

[Fabian Duerr]

Can you please clarify how your data source looks like Maybe with a screenshot or a table...

[Hesloan Viana]

Hey fabd, its a simple data source like this:

 

DATE	RECURRENCY USER
JAN	A
JAN	B
JAN	C
JAN	D
JAN	E
JAN	F
FEB	A
FEB	B
FEB	G
FEB	H

 

 

 

 I wanna show this is numbers, like: In february, when compared to january, we've gained 2 new recurrency users (G and H), lost 3 recurrency users (D, E, F) and maintened two users (A and B)

 

 

[Fabian Duerr]

I think you can solve this with three calculated columns. But to do so you have to make sure that Spotfire uses the right order. At the moment your date column is most likely a string and Spotfire will sort it alphabetically. So, please create some [iNDEX] column first.

Your 1st calculated column will get you the previous month for each user.

Max([DATE]) OVER (Intersect([RECURRENCY USER],Previous([iNDEX])))The 2nd calculated column will get the previous month.

Max([DATE]) OVER (Intersect(Previous([iNDEX])))And the final column will set the flag for you ifthe user is recurrend or new.

case

when [uSER_PREV_MONTH]=[PREV_MONTH] then "REC"

when ([uSER_PREV_MONTH] Is Null) AND ([PREV_MONTH] Is Not Null) then "NEW"

endYou can use this column for your final visualizations like cross tables