Is there an expression for this or is a script change the way to go?

[#1]

Consider the following table:

Customer ID	Wave	Use Profile
101	1	Light
101	2	Light
101	3	Medium
102	1	Heavy
102	2	Heavy
102	3	Heavy
103	1	Medium
103	2	Light
103	3	Medium
104	1	Light
104	2	Medium
104	3	Heavy

I would like to create a chart to look at how the [Use Profile] changes for customers over time.

For example: I would like to be able look at just the customers who were a "Light" user for Wave 1 to see what their distribution looks like over subsequent waves. The desired expression should be able to produce something similar to the below pivot table for the sample data above :

Wave	Use Profile	Customer Count
1	Light	2
	Medium	0
	Heavy	0
2	Light	1
	Medium	1
	Heavy	0
3	Light	0
	Medium	1
	Heavy	1

I have a few ideas of how to implement this in script, but I would prefer to use set analysis for this if possible. Any Ideas?

Thanks,

Clark

[swuehl · Accepted solution]

Maybe create a chart with dimension Wave and Use Profile, and as expression

=count({<[Customer ID] = p(), [Use Profile]= >}DISTINCT [Customer ID])

then create a listbox for User Profile and select e.g. 'Light'.

[#3]

Thanks! Had to tweak it a bit, but using p() did the trick.

I had never used that expression before - great to know about it.

Thanks,

Clark