Hesloan Viana Posted April 20, 2021 Share Posted April 20, 2021 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). Link to comment Share on other sites More sharing options...
Fabian Duerr Posted April 20, 2021 Share Posted April 20, 2021 Can you please clarify how your data source looks like Maybe with a screenshot or a table... Link to comment Share on other sites More sharing options...
Hesloan Viana Posted April 20, 2021 Author Share Posted April 20, 2021 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) Link to comment Share on other sites More sharing options...
Fabian Duerr Posted April 21, 2021 Share Posted April 21, 2021 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now