Brand Indexing

A simple form of indexing we often see is to base on the row average rather than on respondent column counts. This is easily achieved by making the local base avg instead of the usual cwf. Taking the Demo job variable BIM as an example, to index the statements

• Set the local base for Statements to avg

Then plot as a radar chart, row percents:

To check, roll the chart at MA=number of statements, here 10.

The points should all converge to 100.

This shows that on average the index value is as much above as below 100.

For double indexing, the procedure is a little more complicated. Taking Brand Bought Last with indexed demographics, the spec is

Very Important! The runtime flag Arithmetic over Stats MUST be ON to get the desired order of operations.

Note the local base for BBL is tot, and that there is an Average column for checking. If OK, every row average should be 100. Again, display as row percents, but supress the % sign.

The table is

And the averages are

To check Brand 1, less than High School, at 88.15

• Clone the table
• Replace the side axis with just the EDU category codes
• Display as frequencies only
• Export to Excel
• Enter the formula as indicated

Entering all those side expressions is a pain, however, so being the lazy type (as all best DP operatives, like software developers, should be) I wrote a script to batch process a set of double-indexed tables. The script is

''' Generate double indexed tables as column percents as percent of row total percent
''' eg for code 1, 100*(100*c1freq/colbase)/(100*rowtot/grandtot)

#include $RubyUtilitiesLibrary.vbs
#include $RubyReportsLibrary.vbs
#include $RubyVariablesLibrary.vbs

''' user parameters
filt = ""
wght = ""
TOCfolder = "Indexed Reports"

Sub Main
   
   Initialise

   ''' user to enter top and side variables for indexed table
   Index "BBL", "EDU"
   Index "BBL", "Gender"
   Index "BBL", "Region"
   ''' etc
   Cleanup

End Sub

Sub Index(top, side)
   ''' needs ArithOverStats ON
   ''' must be set before table generation - will change the default
   rub.SetTableProp RuntimeFlags","ArithOverStats",true                    
   numcodes = rub.System("Variable", "Codes", side)
   ''' base row
   sideaxis = side & "(cwf;"                                              
   for i = 0 to numcodes-1
     ''' CodeForIndex returns the code at the nth position
     thiscode = rub.System("Variable", "CodeForIndex",side,i)              
     codelabel = GetCodeLabel(side, thiscode)
     sideaxis = sideaxis & "#c" & thiscode & "%cwf%\Index: " & codelabel & "\;"
   next
   sideaxis = sideaxis & ")"

   GenTab "Index_" & top & "_" & side, top & "[tot](tot;*;avg%)", sideaxis, filt, wght
   RowPercentsOnly
   SetTableProp "Cells", "PercentSign", false
   DecimalPlaces "Percents", 2
   RefreshRep
   SaveAndTOC TOCFolder

End Sub

You set the overall filter and weight and TOC folder here:

''' user parameters
filt = ""
wght = ""
TOCfolder = "Indexed Reports"

And add/remove tables here

''' user to enter top and side variables for indexed table
   Index "BBL", "EDU"
   Index "BBL", "Gender"
   Index "BBL", "Region"
   ''' etc

Running the above in the Ruby Demo job delivers these three reports in the TOC folder called Indexed Reports.

There are lots of variations – you could add another parameter to the Index subroutine to pass a local filter, you could use GetActiveDoc() to apply the indexing to tables interactively, and so on.