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

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

Hi Siang,

Drop Customer ID to filter shelf as show below.

Thanks,

Sreekanth.

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

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

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

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.

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

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?

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

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

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

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!