Best Way to Create a Dynamic Table Builder

[Jagsfan82]

Hey all,

I want the user to be able to build a dynamic table.  The dimensions are relatively easy, as I have them loaded in their own 1 field table.  User selects which ones they want and I use the SubStringCount as the calculation condition for the possible dimensions.  

As far as the expressions, this gets a bit tricky.  In general they can choose to calculate two different fields (Lets say Sales and Commission).  You could break those into about 6 different categories, which I all have coded into different variables (e.g: vSumSalesA, vSumSalesB, vSumComA, vSumComB).   The user also might even be interested one level farther.  Possibly they want the average type A sales.. or they want the % of Type A sales vs All Sales.   So if they want the percent its gotta be vSumSalesA / vSumSalesTotal, but if they want the average it might be vSumSalesA / vTransactionCount.  

My original thought was to use 10 variables to store up to 10 user selections.  I used SubField to find the first selected metric, the second selected metric, and so forth.  This gives me the "Expression Name", which I then feed into a Pick(Match()) variable which returns the expression I need.  

Pick(Match([USERINPUT],[EXPRESSION NAMES LIST])[EXPRESSION VARIABLES LIST]) 

The problem is since everything is happening in real time the table is getting confused and not calculating correctly.  Even if it did, it's still very slow.  

Long story short, there's probably many different ways to do this, but I have no clue what might be the best and perform the fastest.  Many of my users will probably have basic Qlikview navigation skills, but I don't want them to have to use Set Analysis to build a custom table.  I attached a few pictures.  The "Create Table" and "New Table" are not currently relevant.  I thought I could use them to basically hold the calculation of the table until all selections have been made.

Any thoughts or ideas are appreciated!!!

[Jagsfan82]

As I thought about this over the weekend, my current plan is to build a sort of mapping table for my expressions.

//PSEUDOCODE

//DEFINE VARIABLES
set vSumOnlineSales = Sum({Type=ONLINE} Sales)
Etc.... //Repeat as needed

QUALIFY
CustExp_1:
No, Metric Name, Variable
1,Sum of Online Sales, vSumOnlineSales
Etc... //Repeat as needed

CustExp_2:
No, Metric Name, Variable 
Etc... // Repeat for the number of custom variables needed

I can then walk the user through selected Expression 1, Expression 2, etc... using list boxes of the Name.  I'll then use "GetSelectedCount(CustExp_1.No)" as the expression condition.  I can use CustExp_1.Variable as the expression (expanded to function correctly) since there will only be one selection for each table.  

My concerns if anyone has any thoughts on are:

• This should work well from a performance standpoint (I think?), but  I do wonder if having 10 extra floating tables in the model is a problem. I assume not since they will only be 3 fields and 30ish expressions. 
• I also don't love having the user scroll through 30 (or potentially more as the solution grows) pre-built expressions.  I was considering adding additional fields that can act as a filter.  For example Sum of Online Sales would qualify as "TYPE = ONLINE" and "AGGREGATION = SUM" and "MEASURE = SALES".  While selecting which expressions they want they could click on any of those to show all Online or sum or sales expressions.

All-in-all i don't think there's a perfect solution, but my current thought is this would be the best as far as implementation, maintenance and updating, performance, and user experience.  Any thoughts otherwise or counter-arguments would be appreciated