This post looks into using a pretty cool visualization technique called a Tree Map to represent the data that would traditionally be looked at in a Cross Tab.

TreeMaps from the Beginning

Tree Maps are an Area oriented way of showing data, for instance if you had some Top-Line responses to a question as follows:

It’s TreeMap respresentation would be as follows:

This provides a very instant read for the viewer who can immediately tell not only which the largest and smallest segments of their sample are but also what the real proportions are. Therefore it’s doing the job of a sorted table that has percentages. In essence its an Analog view of the data much like comparing an Analog to a Digital watch.

why Treemaps suit Tables

The tables we get all the time in market research put a lot of information in front of the person who’s trying to make gain insight. This kind of table is really common:

There’s really  a lot of information for the brain to consume in order to build an insight, its all very digital and it takes a long time to process. The Analog view of this is very different:

But Oh! There’s More

Tree map’s have 2 main visual indicators available to them, the first being Area, the second being Color, so far the examples have had both driven by proportion of responses. Here’s an example where the Color is being driven by something different, in this case, the Color on is being driven by  Mean score on another question (the proportion is still being driven from the sample size).

Not only can you easily tell that the 55-65 age group is rating a product pretty high but you also have the visual warning that the insight might be based on a fairly small sub-group.

Wow, how did you do this/How can I do this?

There’s no cheating, we built an app platform quite a while ago now for feeding on survey results which we use for all kinds of visualizations. Then we built a demo app on the top of that which presents data from regular/real surveys into a TreeMap.

The demo app is built using the Microsoft Silverlight Toolkit, and is reading data from our insightMart data warehousing system (the data is sample data and didn’t come from a real market research study in this case).

We can build this kind of visualization and many others for people’s dashboards or presentations very quickly, the data doesn’t even have to come from survey’s.

I’ve changed the colors here but the essence of the problem was that I had a scale which looked something like this (actual colors changed):

In other words a gradient brush scale, such as:

<LinearGradientBrush x:Key="MyTestBrush" StartPoint="0.05,0" EndPoint="0.95,0">
   <GradientStop Offset="0" Color="Red"/>
   <GradientStop Offset="0.5" Color="Green"/>
   <GradientStop Offset="1" Color="Blue"/>
</LinearGradientBrush>

In my case the brush had far more colors than this in its scale and the particular offsetting of the start and the end of the gradient was important.

I’d previously stumbled on this post about making custom interpolators for the Silverlight Data Visualization Toolkit.

So I set about the task of making a LinearGradientBrushInterpolator trying to re-use as much as possible from the toolkit itself.

First off I looked at the way the SolidColorBrushInterpolator worked, it already did a big part of what I needed to do by interpolating a range between 2 different colors.

[sourcecode language='csharp'  padlinenumbers='true' light='false' autolink='false' gutter='false']
namespace ColorPicker
{
    public class LinearGradientBrushInterpolator : Interpolator
    {
        System.Collections.Generic.List interpolators = new System.Collections.Generic.List();
        LinearGradientBrush templateBrush;

        public LinearGradientBrush TemplateBrush
        {
            get
            {
                return templateBrush;
            }
            set
            {
                if (value != templateBrush)
                {
                    templateBrush = value;
                    interpolators.Clear();
                }
            }
        }                                                                                                                   

    }
}
[/sourcecode]

In other words I decided to use an array of SolidBrushInterpolators to do this. To set these up I wrote this:

[sourcecode language='csharp'  htmlscript='false' padlinenumbers='true']
 private void InitializeBrush(LinearGradientBrush br)
        {
            if (br == null)
                throw new ArgumentNullException("br");
            //set start and end interpolators
            double start = br.StartPoint.X;
            double end = br.EndPoint.X;

            if (br.GradientStops.Count < 2)
            {
                throw new Exception("2 Gradiant Stops must exist");
            }

            SolidColorBrushInterpolator startInterpolator = new SolidColorBrushInterpolator();
            startInterpolator.DataMinimum = start;
            startInterpolator.DataMaximum = br.GradientStops[1].Offset;
            startInterpolator.From = br.GradientStops[0].Color;
            startInterpolator.To = br.GradientStops[1].Color;
            interpolators.Add(startInterpolator);

            for (int i = 1; i < br.GradientStops.Count - 1; i++)
            {
                SolidColorBrushInterpolator inter = new SolidColorBrushInterpolator();
                inter.DataMinimum = br.GradientStops[i].Offset;
                inter.DataMaximum = br.GradientStops[i + 1].Offset;
                inter.From = br.GradientStops[i].Color;
                inter.To = br.GradientStops[i + 1].Color;
                interpolators.Add(inter);
            }

        }
[/sourcecode]

Because of my Gradients not starting or ending on 0 or 1 I had to add special handling of these beginning and end cases.

Finally I had to implement the Interpolate method itself:

[sourcecode language='csharp' ]
public override object Interpolate(double value)
        {
            if (interpolators.Count == 0)
            {
                InitializeBrush(templateBrush);
                if (interpolators.Count == 0)
                {
                    throw new InvalidOperationException("TemplateBrush is not optional");
                }
            }
            double min = 0;
            double max = 0;
            if (double.IsNaN(DataMinimum))
            {
                min = ActualDataMinimum;
            }
            if (double.IsNaN(DataMinimum))
            {
                max = ActualDataMaximum;
            }

            if (value < min)
            {
                value = min;
            }
            if (value > max)
            {
                value = max;
            }

            //calculate relative interpolation
            double offset = (value - min) / (max - min);

            foreach (SolidColorBrushInterpolator inter in interpolators)
            {
                if (offset < inter.DataMaximum)
                {
                    if (offset < inter.DataMinimum)
                        offset = inter.DataMinimum;
                    return inter.Interpolate(offset);
                }
            }

            return interpolators[interpolators.Count - 1].Interpolate(interpolators[interpolators.Count - 1].DataMaximum);

        }
[/sourcecode]

The end result worked nicely and I put together this TreeMap to prove it to myself:

The live version of this sample (which randomizes each time you refresh is here).

Full Source code including the sample application is here.

PASW sure got some eye’s rolling, although it was suggested to be an acronym of Predictive Analytics Software, my favourite theory is that it was an available 4 letter domain name which could be made to fit.

Anyway with the PASW change, all the products SPSS sold were renamed. Therefore well known products like SPSS Base became PASW Statistics. Which brings me on to the products that other products for survey research that had previously been under the banner SPSS Dimensions with product names such as mrInterview and so on, Tim Macer said some fairly inspired words on it here http://bit.ly/155Ll3.

Because the aforementioned trademark dispute was settled another rename has come, and PASW is to become IBM SPSS (http://bit.ly/10qexj).

Best Effort At Decoding the product Names

My Opinion

If it matters, and I have no idea whether it does although I am an SPSS Customer and I do spend a good deal of my time working with this line of products for my various customers.

This is diluting your brand IBM SPSS, I know you are going for enterprise sales and it makes is easier to sell the concept of IBM SPSS Data Collection as an end to end solution (which it is at one level). However:

The tools still exist and the product names just stink of a category dump which may make it easier for you to internally manage things but are completely divorced from any real concept of branding.

its the same as Heinz deciding to rename Heinz Baked Beans to Heinz  Canned Foods Tomato With Beans and running an ad campaign on it.

This may make it easier for Heinz to explain its product at an enterprise level when its talking to Walmart but it doesn’t mean it makes any good marketing sense.

In terms of what happens in opinion research, the last couple of blogs have really been about fermentation and bottling; its all been about warehousing data. Little has been said about getting the data in or the process which we call Harvesting.

What are the Grapes?

While I am pressing (excuse pun) on with this analogy, a grape is an opinion; opinions about your business or one of its brands are not concrete things. There are potential grapevines all over the internet, internal information systems and there’s still of course surveys; either survey’s that you commission or industry reports that you are savvy to.

These sources of information vary wildly in quality, structure from a raw data point of view. Somehow you want to bring all or some of these sources to paint a full picture, one that can drive some action and deliver improvement.

HArvesting

At this point I could paint a rather blissful picture of the process of harvesting which looks like this:

To combat the disconnection between the simplicity of this diagram and the actual complexity involved in shuffling through data and getting it to sit nicely together in a warehouse. We developed the concept of Harvesters which quite literally take a stream of data and “do stuff” to it in order to make it sit nicely in a standard location for analysis and reporting.

There’s no such thing as a free Opinion

Structured data like that coming from surveys or CRM systems is relatively easy to deal with but rarely standardized. It’s also of relatively known quality, but there’s no surprise that you pay to get it (you either pay your call centre, web site, survey systems/vendors). Therefore this is the cheapest data to harvest.

Syndicated research (e.g. industry reports/omnibus data) data sits slightly in the middle of the spectrum, its got a structure (usually quite a variable one) but its aggregated so its limited, and some historic data also fits into this part of the spectrum.

Conversely unstructured data like facebook, twitter and blogs are almost without structure, generally need some categorization which can also mean a partly human process therefore the harvester is potentially a process in its own right.

Whichever way you look at it, you either pay more to grow the grapes and less to harvest them or the other way around.

Keep focussed on the Barolo and You will Pick the Right Grapes

Throughout the process of building a warehouse, you have to stay focussed on the wine you are trying to make at the end. The un-harvested grapevines will still be there. There’s been too many attempts to build warehouses without a clear vision of what’s got to come out of the other end.

“lets grab all the data we possibly can and see if there’s anything there”

This is NOT the idea, you will never get a good glass of wine from a bottle of random grapes without huge amounts of luck.

If you are interested in finding out more about what we do, or what value it can bring visit www.forgetdata.com or message me personally on twitter @samwinstanley

“survey data is simple, you should try building an analysis database from our customer records…my life is hell”

I shrug this stuff off, its easy to pick on the little guy who isn’t building the systems that have 7 figure budgets and 100 strong programming teams.

Thinking Backwards…

Researcher’s actually think backwards, they know they are digging for information about how things perform against each-other, they come up with the instrument to measure this data, possibly in terms of questions and answers, possibly in terms of qualitative analysis, web mining etc.. however the start is the business problem. Often this transpires into some kind of interview script which has modules and questions and sample variables etc… what just happened was that something very business specific into something deeply generic (questions and answers). This is what I call “downgrading”.

For years and years software vendors have been working very hard to build systems for collecting and analysing data for surveys, and all of them have focussed on ease of creating surveys. In other words all of them help convert business minded ideas into generic questions and answers.

Ease of Authoring Might be a BAD thing..

You see the truth is that ease of authoring is a pseudonym for data-hell, it means that a relative novice can create an immense survey with no thought whatsoever to the real structure of the data that’s coming out.. an entire DP industry and countless DP tools have been founded on creating order from the chaos that is surveys.

We Revolve Around Cross-Tab Reports

I refuse to do an extensive industry study to establish how many cross tabulation tools exist for market research vs. the rest of industry. Whatever the hard numbers I’m certain its extremely disproportionate to the market size.

It’s clear why the market research and survey research industry loves this kind of technology so much though. It’s allowing primitive information like questions and answers to be “upgraded” back into a business context so that it can be analysed.

So are cross tabs a bad thing?

Cross tabs have a role, they are an analysis tool more than a reporting tool, its a fairly neat way to compare X with Y in quite an ad-hoc fashion it enables exploration of results.

However cross tabs bring baggage with them when you use them as a reporting platform in a couple of ways:

• Cross tab reports are nearly always showing a single source of data and that data is questionnaire structured not business structured. This means every piece of information you put into this data source is getting “downgraded” and its loosing richness and its context.
• Cross tabs naturally limit analysis, we “the techies who provide to the MR industry” try to force statistical models into these reports in a very un-natural way… for instance nobody else, anywhere outside surveys has really made use of a Column Proportions test which compares column A with B/C/D/E, that doesn’t mean that nobody else in the world ever does that kind of analysis it just means is a pretty absurd way to implement the result of a statistical test.

Preaching to the converted

Much of the survey research world has moved on from cross tabs at least at some level, and I’m not out to teach people to suck eggs. But I see so much evidence that traditional DP and interactive tab  packages still provide lots of underpinnings for the industry.

The industry is bound in chains but contains many smart people who do understand that if you think up-front about the way to arrange data, you can provide a much more valuable analysis and reporting experience on the back of it. What we lack is the 7 figure budgets that make it so easy for banks and blue-chips to roll elegant warehouse systems and dynamic business dashboards on the back of it.

Back to You Mr Database Guru

At the start of the post, I stated my poor treatment by my peers in the banking world. The truth is they live in a world of luxury, if they thought they had a hard life now whatever would they do if 100’s of people in the organisation were able to add a new data field into a customer records.. but that’s virtually the same thing as adding a new question to a questionnaire. Seriously, those guys would suck at surveys in every way…

At forgetdata we spend a lot more time thinking about and designing the strategy for managing and storing survey data than most people. We understand that perfect standardisation of questionnaires isn’t a perfect world its a rare luxury and that even 6 figure data architecture budgets are even rarer.

Our passion is helping the industry solve this kind of problem without having to re-invent every wheel its based on, a sensible business based highly controlled kind of chaos.

When analysing markets or business performance, analysts and researchers are faced with this kind of task all the time. Forced into a position to be able to give a certain figure for whatever the KPI in question is, this number I will call 5.6 just for consistency, it matters little what 5.6 is it could be a measure of anything. For instance “on a scale of 1.7 the average overall customer satisfaction was 5.6 for the last quarter” it can be a pretty important number this 5.6….

If the Answer was 5.6 what was the question…..

For a long time in the world of survey research arriving at such a number was a simple task relatively speaking.. you would run a survey, perform some data processing and somewhere probably in a cross tab report clearly stated there was the Overall Satisfaction question with a Mean score of 5.6.

However things are changing, the source of 5.6 is getting spread out, here are some of the things that could be considered now as evidence that “on a scale of 1.7 the average overall customer satisfaction was 5.6 for the last quarter” based on the world as it looks now (and this list is not exhaustive):

• The responses to several surveys quite possibly surveys that don’t exactly match some from in-house research, some bought in from outside sources.
• Research from actual performance in CRM and mining what was said in emails and conversations between the company and its customers. For instance if based on 1000 respondents the overall satisfaction was 5.6 but based on 10,000 calls to customer service, 85% of people said “I hate this company and I never want to do business with it again” its hard for an analyst to ignore this data if its available when arriving at 5.6.
• What is said on the internet about a business in social networks and online communities can and more frequently is being listened to as evidence of 5.6.

Where’s My Data?

Given that 5.6 is considered important, possibly even life changing from the point of view of an analyst or researcher, its pretty important the the evidence supporting it is very solid. So solid that if somebody different were asked to reproduce the number 3 years down the line after the life changing decision they can do.

Relating back to the expense claim, its traditional to write down all the line items which amount to your number in a spreadsheet, and sometimes even scan all the evidence supporting those numbers and attach it.

How to Know Where your Data is and PRofit?

An initial part of any kind of analysis to arrive at 5.6 usually involves getting all of your data into 1 place and somehow normalising it to make it comparable. If 5.6 was the only answer you needed then probably making a spreadsheet and doing some analysis in Microsoft Excel would be quite okay. Unfortunately 1 number is never enough and 1,000’s 5.6’s are usually needed.

To produce this kind of information quickly and efficiently either on demand via a Dashboard or online report, or distributed in a periodic report you have to build a process to support it along with the technology to support that. You need a common repository or warehouse to put all of your data in to enable you to get it out in a structured way.

At forgetdata we’re pioneering a type of data warehouse that allows data from diverse sources to co-exist, that enables these worlds to meet and we’re building the tools around that to support the getting the data in, analysing it and reporting from it.

There’s lots of general benefits to having a warehousing system in place that I’ll try to discuss in future articles, but its clear that making wide use of this kind of warehouse system enables lots of new possibilities both in terms of analysis and efficiency.

Honestly, I’m not a serial ranter most people who know me would say I’m a fairly laid back kind of person, and they would be right until this chain of events…

• On 4th September I received a couple of letters in the post from BT One letter said “Welcome to BT, <fluffy saying how great it was I’d opened an account with them”, the other letter said “please complete and sign the following Direct Debit mandate for £22.50/month” or words to that effect.
  • I was confused I had a BT account which had remained intact for at least 5 years I had never changed it and indeed I checked my bill and the Account number on these letters did not match the account number on my bill, but the telephone number did.
  • My new service would start of 4th September which was the same day I got the letters so I thought I should check this out pretty fast or risk getting sucked into a “mystery contract” from BT.
• I phoned up customer service, they didn’t understand what had happened at all, however they thought it was all likely some glitch and promptly shut down the “mystery account number”. I made a complaint I wanted to know what had happened.
• The next day a very jolly lady phoned me up, she was clearly a kind of “anti-churn CRM master” of some kind, she was so deeply apologetic and explained to me that my account transitioned between different internal BT systems and that usually no letters would be sent out and I would see no change except my next bill would have a new account number on it. However something went wrong. She reassured me that everything would be okay that my old account number was still working just fine and not to worry.

At this point I’d spend 2 hours on the phone to BT but at least felt that something had been achieved.

On Monday 7th…

the Phone went dead..

A voice in the back of my head was telling me that this would happen before it actually did, the unstoppable ball had already started rolling in BT, the hasty Customer Service rep I had initially talked to had shut down my account, he didn’t realize he was doing it he thought he was shutting down a strange ghost account but no.. he shut down my account.

My satisfaction at this moment in time was somewhere between Highly Dissatisfied and Completely Dissatisfied, I was scoring about 1.4 on BT’s own Sat scale.

However I thought, this might not be too bad, I’ll phone up tell them “I’ve been disconnected in error, please sort it out”. After several hours on the phone with several people the information I’d been getting back from BT had moved from “Actually sir you have never had a phone account with us, your line is with another provider we just do your broadband” to “Okay sir we understand what’s happened, we’ll transfer you to sales because only they can reactivate your account” reluctantly I agreed to accept my 5th transfer of the evening… On came the sales rep and we reached this kind of point:

Sir we can sort this out, you will need to pay a £5.95 connection fee, sign up to a new 12 month contract and within the next 5 working days your line will be active again

Well I declined this kind offer, and explained the circumstances again very clearly and asked to be connected to a person who could reconnect me without a fee and without a minimum term contract. I discovered that Sales people dislike this line of action, predominantly because 2 different people CUT ME OFF when I uttered these words.. each time after I was cut off customer service would call me back and repeat the process of connect me to sales, each time I said those words the sales person (who is only scored on revenue generation) cut me off. Since then the Customer Service team have evidently given up as no word has been heard from any of the large quantity of people who all appear to be called Michelle (I’ve decided the name is culturally unlikely given what has become very wide spread research now) have attempted to contact me.

But all stories have a happy end…

With Dell it was HP who they lost their business to, with BT I’m really happy to say it was thephone.coop given I was beginning to conclude that the wheels of BT were actually unable to turn in a way that would reach a satisfactory end because the computer was saying NO regardless of the number of cheery people of sub-continental people called Michelle were thrown at it. I phoned up thephone.coop, I use this supplier for all my office telephone/internet needs. Within 20 minutes I’d started a new contract which was cheaper, on my old number (which BT wholesale still have to reconnect and will still take up to 5 days whatever happens).

But there’s more to my choice than that, thephone.coop just shines at customer service, every time I’ve had any cause to contact them, 1 person has been able to see or find out the whole picture. They are a company with values and it shows down to every single thing they do. Ethically they are great, they don’t just say they are different, they actually are different right from the core and for me at least it works. So if nothing else they deserve the viral marketing so here it is, my token effort.

Installing the new disks into my trusty HP server was easy, it can hold 4 disks and it has support for Hardware RAID. I have a spare slot still and I will put the RMA’d failed disk into that when it arrives and use it as a backup device.

The net result of all this thinking was that some thinking and re-arranging of stuff was going to leave me with new drive letters for my Hyper-V area, as a result some configuration changes are needed to pull this off. On the net I’ve seen several somewhat hacky ways to do this but I wanted to do it what I think is “the right way”.

This is the approach I took:

1. Export each of the VM’s to the new disk using the Hyper-V manager’s built in Export feature which I would say 2 things about:
      – is rather slow to be polite. I am certain it would be faster if I paid an exhorbitant amount of money for expensive disks but still this is SATA-300 to SATA-300 on the same machine and exporting 120GB took somewhere over an hour.
      – in general there should be an optimized way to just “move a VM” along with all its stuff. Exporting is more made for moving between machines.
2. Delete the VMs in the Hyper-V manager (gulp)… gotta hope that export was successful.
3. Change the Overall hyper-v settings to point at the new mirrored drive/paths.
4. Import each of the virtual machines. This is very much faster than exporting thankfully.

After my last blog post about using auto-categorization in Desktop Reporter, I was asked the question:

“How do I analyze multiple response data which is coming from Excel”

Here’s a walkthrough of 1 way that you can handle it.

The Data

I started with data which is actually a profile extraction of the Museum database that I exported to Microsoft Excel which looks as follows (in Excel):

Museums_Visited is a multiple response which is comma separated.

Splitting out the categories.

Before we can really work on the data in Reporter we need to make a couple of tweaks to it in excel.

Inside Excel – Select the Museums_Visited Column then click on the “Text to Columns” button:

This starts the conversion Wizard.

The end result of this is a brand new column or 2, actually 5 new columns in my case, there is one column for each response to Museums_Visited and the most responses anybody made to that question is 5. (This is what SPSS Base users will commonly call a “Category Set”).

Now the data is in this shape we can take the rest to Reporter.

Opening up in Reporter

We open up the Excel file in Reporter and it recognises each column to be Text, Text is not great for doing tabulations! it needs some work…

To make the Text variables usable for tabulations we need to categorize them, and we do that using the new Categorize feature, as follows:

Note I have unchecked the “Other” option and very importantly Not asked (NULL) is checked FOR the first category only this point is very important for a reason I will mention later, the second variable should be categorized with these options:

Note that for this one the User Missing Category is checked.

And repeat this for all of the 5 new variables until we have along with the other categorical variables in the data until we have (note that there are new variables and these have new icons):

The final step is to make a new variable that combines our Museums_1-5 into a multiple response.

Hit the select button which opens the Expression builder… We want our new variable to add together all the individual responses to the Museums_1-6 variables to make one new multiple response variable.

Click okay, Desktop Reporter does some work in the background and this screen now has responses in it.

Click Save and close to save this new variable into the data.

Then Export the data to a new format as follows:

Then open the newly exported data.

Note here that I am opening an MDD file after exporting a DDF file.

Drum roll Please

There we have excel multi-response data tabulated in Desktop Reporter.

Some final notes

1. Why did you set up the first Museum variable to use NULL and the others to not use Null.
   I knew I was going to make the multiple response variable which had the expression Museums_1+Museums_2+Museums_3 etc.. I also knew that Museums_1 was the real base of the variable, (e.g. people with a blank to Museums_1 actually did not answer that question). The Dimensions DataModel has special ways of dealing with the NULL value (this is called the ANSI SQL 92 way to handle nulls). As a result 1+NULL=NULL…. In our case if Museums_1 had a value and Musuems_5 did not then the end result of Museums_1+….+Museums_5 would always be null which is definitely not what was expected.
2. Why so many steps.. it must be possible to make it simpler.
   All of these steps can be automated if you are comfortable with mrStudio and particularly with TOM scripting. In the end Excel isn’t a great data format, especially when you talk about multiple response data and I think Reporter is doing a good job of getting around some of those weaknesses. This is a slightly specialized case because in the end if the data you have in excel is really simple like this you can achieve a good result just using excel itself.
3. Why the export to DDF, why not just analyse in the Excel format.
   There’s definitely a performance reason why it’s a good idea, but I also did find some problems with making tables before I did the export. I suspect there were ways to workaround these but I’d already decided I was going to export the data to something that could natively hold multiple response data.

I’ve spent the past week or so testing all of the different forget data tools and solutions with this new version which hasn’t turned up any difficult compatibility issues.

The user interface, is the same great interface we’ve all come to know and love, there’s really no significant changes since version 5 (which itself didn’t change very much since 4.5).

The new features fall into 3 main areas:

• Auto categorization of text, numeric and date variables
• A new netting feature.
• Significantly enhanced data management and data exporting features.
• One last, really important thing at the end.

Auto Categorization

Reporter and its predecessor mrTables have always supported some level of analysis on Text, Date and Numeric variables, for instance you could always create a report of Mean of Age, Max of Age etc. and you have always had the ability to create derived variables with custom elements in them. However its a lot of work to do this…

If you open the SPSS sample database called “Household” there is a variable called Vehicle.Color which is a text variable with a number of distict values as follows:

Up until now, you have had a number of approaches available to deal with these variables:

1. Write a new categorical version of the variable with all of the right categories in it then write some script (either DMOM or SQL) to populate that variable with the values.
2. Use one of the scripts from the DDL to attempt to auto-generate an axis expression for the variable that can be added to the variable.
3. Manually set up User Defined Categories for the variable in reporter.
4. Have somebody manually code the variable.

The first 2 options really only exist if you have a DP process in place for doing this kind of work. Additionally options 1 and 3 both create quite a bit of potential maintenance if you have variables which can hold a lot of values.

If you only use Reporter, then your only option was option 3 and that involved a lot of repetative work manually creating each category.

The new way is very much smarter.. you can simply right-click on a variable and select the new Categorize option:

Which opens up a new screen called “Simple Categorization”:

By default, this screen creates a .Codes variable for the main variable (this is also known as a Helper field), this is the place that special responses such as DK, NA, REF get stored when they exist on a Text/Numeric/Date variable.

The Preview screen is very useful here and shows you what you are going to get before actually generating the new variable:

After going through this process what you end up with looks like this:

And this new variable can be used for reporting in the way you would expect. Pretty good eh!

but what’s it actually done
Some of you might be wondering what’s happened under the covers here. Well to find this out we’ll export the Metadata and have a look at the new variable.

Looking into this exported metadata the Color variable now looks as follows:
color “Color”
text
helperfields (
“Codes” “Codes”
[
IsUserDefined = true
]
categorical [0..1]
{
Black “Black”,
Blue “Blue”,
Green “Green”,
Pink “Pink”,
Purple “Purple”,
Red “Red”,
Red#20and#20blue “Red and blue”,
Red#20and#20yellow “Red and yellow”,
Silver “Silver”,
Too#20rusty#20to#20tell “Too rusty to tell”,
White “White”,
Yellow “Yellow”,
“other” “Other”
} expression(“Categorize(color,”"color.Codes”")”, NoDeriveElements) usagetype(“HelperField”)
helperfields (
“other” “Other”
[
IsUserDefined = true
]
text;
);
);

The sharp eyed will see… a new Function in the DataModel, this is not some kind of Desktop Reporter faking there’s new functionality that you could get at in script here too. The Categorize function itself does not seem to be documented, but there is a new funtionality in the Tables Object Model which you will find in the version 5.5 DDL (http://www.spss.com/ddl) under CreateCategorizedVariable. Generally all the function library functions do get documented so its probably only a matter of time… or perhaps I just failed to find the documentation in the DDL.

Overall, this is a great new feature and there’s a lot of times that this will save time, I see it being particularly useful for working with variables that are coming in from Sample that are often text based.

A new netting feature.

There are plenty of times when you would like to create a Nett of some categories but you have some items which can fall into multiple Netts in the variable. For instance in a list of brands you might want to create a Nett called “Market Leaders” and another Nett called “International Brands” and some categories would fall into both netts.. The new feature is called “Nett and Keep” which SPSS calls “Net and Keep” and it creates a Nett of some elements but leaves the elements in place much like the old “Combine and Keep” feature.

This is useful, its not a show stopping feature.

Data Exporting updates..

Release by release, Desktop Reporter seems to get more and more viable as a DP tool rather than just a reporting tool, possibly much to the horror of DP people. All of this is via the feature “Export Data” which in allows a reporter user to create a custom data extraction which has the following features:
- Filters cases according to desktop reporter features.
- Allows a subset of variables to be extracted.
- Includes newly created derived variables and in the resulting data and any axis expressions.

It’s not completely comprehensive but its very usable and with this release of reporter there’s the following enhancements:

• Export levels data to SPSS files (to be honest, I had problems getting this to work as I would expect with the household data, I only got one output file).
  
• Export Factors – Up until now, when you exported data from Desktop Reporter, the values of categorical variables always were the real, datamodel values. If you are exporting to a native dimensions format its fine, but if you export to CSV its a real pain.. Lets say you have a scale question like
  Extremely Happy(5), Quite Happy(4), Neither (3), Quite Unhappy(2), Extremely Unhappy (1). In CSV you might very well end up with a variable that has values like 324, 325,326,327,328 and these values were unusable without metadata. With this new feature the Factor values (or scores) get exported and this is much more usable.
• New Data Format - DZF – Compressed Dimensions File. Many of you have probably noticed that DDF files are quite big on disk, but they compress very small. In my test a small export was 160kb uncompressed and 2kb zipped. This is not a real new format, its just compressing an existing one, in fact if you access a DZF file it becomes a DDF.. its useful for emailing data though.

One last thing

When you save an MDD file with version 5.5 of Dimensions you cannot access it using version 5.0 of Dimensions. I am not aware of any fundamental compatibilty issues but its a fairly significant irritation if you are preparing data for people and you don’t know which version of Reporter they use. Opening 5.5 data with 4.5 is reported to be okay, it might be a 5.0 glitch and now I’ve upgraded its not that easy for me to see if there is a hack to get around it….you have been warned.