We've got a rather complex report, based on several thousand data points across 9 tables.
In order to best consolidate the data, we auto-built a series of variables (around 200 of them) which use relationships and/or SQL queries to compile. Most of the variables are sequential (ie,. $$v1. ~ $$v25, etc), gathering similar but sequential data from numerous points.
The biggest challenge we're encountering is how to compile metadata from these variables.
So as an example, let's say that there are
50 variables ($$vs1.1~vs1.51) which handle sales
50 variables ($$vcg1.1~vcg1.51) which handle cost of goods
50 variables ($$vpl1~$$vpl51) which calculate P & L for these 50 product groups
Each of these variables is actually a list of sales, (or cost, or profit) across 45 locations, so each variable contains delimited data of that product with 45 delimited lines of numbers, (integers for sales & CG, and decimal'd percentages for P&L - ie., ".3572").
We need to further condense these variable lists into 2 things:
1. A metadata variable showing the P&L average for say, all $$vpl14 across its 45 locations (compiling all 45 returns)
2. We need a P&L chart showing counts of p&l for products:
So, say of these 50 $$vpl's, we want to create a pie chart, showing three returns:
- count of products with margin lower than 10%
- count of products with margin 10%-25%
- count of products with margin greater than 25%
A - How should I query a delimited variable for averages?
B - How should I query a variable (which is a list of percentages) for 1., 2., 3 above)?
C - Is there a method of searching a list for a value (ie., patterncount; variable; "<xxx%")? I need to query that variable list for a valuecount of a range, not a specific number. how would one pull that data?