6 Replies Latest reply on Jan 5, 2017 1:33 AM by Siang Li Chua

Selecting a group in Year 1 and tracking them through the Years

Siang Li Chua Dec 29, 2016 12:43 AM

Hi. Suppose below is my data file. I can easily plot the number of customer who are happy each year (see below). Now, I wish to plot: Among those who are Happy in Year 2012, how many of them are Happy in each subsequent year (That's the bar chart below should be 3, 1, 1, 1 because Customer 4 is excluded). How should I do it? Thanks.

Year	Customer ID	Happy?
2012	1	Yes
2013	1	Yes
2014	1	No
2015	1	Yes
2012	2	Yes
2013	2	No
2014	2	No
2015	2	No
2012	3	Yes
2013	3	No
2014	3	Yes
2015	3	No
2012	4	No
2013	4	No
2014	4	Yes
2015	4	Yes

1. Re: Selecting a group in Year 1 and tracking them through the Years

Sreekanth Kasaraneni Dec 29, 2016 4:14 AM (in response to Siang Li Chua)

Hi Siang,

Drop Customer ID to filter shelf as show below.

Thanks,
Sreekanth.
Like Show 0 Likes(0)

Actions
2. Re: Selecting a group in Year 1 and tracking them through the Years

Siang Li Chua Dec 30, 2016 3:39 AM (in response to Sreekanth Kasaraneni)

Hi, Sreekanth. Thanks for your suggestion. Sorry that I didn't make my question clear enough. I have thousands of records and so your method can't do. I hope to filter by the criteria: Happy customer on Year 2012 first, then plot the chart for all years.

regards,
siang li
Like Show 0 Likes(0)

Actions
3. Re: Selecting a group in Year 1 and tracking them through the Years

Naledi Hollbruegge Dec 30, 2016 6:48 AM (in response to Siang Li Chua)

Hi,

I'm not entirely sure I understand the question, but if you created a calculated field with

IF [Year] = 2012 AND [Happy?] = 'Yes' THEN [Customer ID] END

This will create a new column that will only return the customer ID for those customers that were happy in year 1. You could then plot this new field throughout the years.
Like Show 0 Likes(0)

Actions
4. Re: Selecting a group in Year 1 and tracking them through the Years

Siang Li Chua Jan 2, 2017 11:20 PM (in response to Naledi Hollbruegge)

Hi, Naledi. Thanks for your suggestion. But the calculated filed are filled with CustomerID only for Year 2012, and NULL for the rest of the years. So when I plot, only Year 2012 appears. The rest of the years are zero.

Any more other suggestions?
Like Show 0 Likes(0)

Actions
5. Re: Selecting a group in Year 1 and tracking them through the Years

Naledi Hollbruegge Jan 4, 2017 2:27 AM (in response to Siang Li Chua)

Hi,

yes, you are right. That doesn't work. I didn't have the time to play with the data before my last response so now that I have done that my next suggestion would be to create a manual group of your customers.
Set up the view with Year and CNTD of customers and filter to only happy customers. Then place the customer ID on the 'details' shelf. Now drag and select all of the bar that represents 2012. Right-click and select 'group'. It should then give you an option to group by different dimensions. Select 'customer ID' and it will create a group with all of the customers that were happy in year 1. That field will now appear in your dimensions where you can rename it. Now place that field on your filters and select only the group you want to display.

See the final setup below. I hope that works out and gets around the issue of having too many customers to manually select them in the filter. Let me know if you have questions.

Best wishes
Naledi
Like Show 0 Likes(0)

Actions
6. Re: Selecting a group in Year 1 and tracking them through the Years

Siang Li Chua Jan 5, 2017 1:33 AM (in response to Naledi Hollbruegge)

Thanks so much, Naledi! Yes, this sounds workable. This is one of the thot I had too, similar to Excel lookup table. But I implemented using another thot yesterday and it works as follows:

Calculated field, Wanted ID: IF [Year] = 2012 AND [Happy?] = 'Yes' THEN 1 ELSE 0 END
Calculated field, Wanted Rows: {FIXED [Customer_ID] : max([Wanted ID])}

Now I plot with filter "Wanted Rows = 1"

Thanks for attempting the solution again!
Like Show 0 Likes(0)

Actions