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.