I encountered a problem, hoping I could find some answer here. Right now, I have 6 scenario (1,2,3,4,5,6), and for each scenario I created a variable with the name like vBest_sales_1 (the month with the highest sales under scenario 1).
Raw data and variables are as below:
date
scenario
sales
customer #
Sep-13
1
6
7
Oct-13
1
88
2
Nov-13
1
8
4
Sep-13
2
59
8
Oct-13
2
95
9
Nov-13
2
77
3
Sep-13
3
39
9
Oct-13
3
98
6
Nov-13
3
88
9
Sep-13
4
90
1
Oct-13
4
90
4
Nov-13
4
64
8
Sep-13
5
51
4
Oct-13
5
47
2
Nov-13
5
9
3
Sep-13
6
27
3
Oct-13
6
32
7
Nov-13
6
2
0
vBest_Sales_1
Oct-13
vBest_Sales_2
Oct-13
vBest_Sales_3
Oct-13
vBest_Sales_4
Sep-13
vBest_Sales_5
Sep-13
vBest_Sales_6
Oct-13
Right now, I want to create a table as below:
Scenario
1
2
3
4
5
6
sales
88
95
98
90
51
32
customer #
2
9
6
1
4
7
Current approach is to have a dimension which is calles metrix (metrix contains sales, customer #). And then I have 6 expressions (one for each scenario.)
Sample expression:
if(metrix = sales and date = vBest_Sales_1 and scenario =1, sales,
if(metrix= [customer #] and vBest_Sales_1 and scenario =1, [customer# ]))
I need to write 6 expressions for this chart, because for every expression ,the variable name is different. My question is can I use
vBest_Sales_'scenario' as the variable name? If scenario is 1, then I get the data Oct-13. I tried this. But QV always gives me vBest_Sales_1, not Oct-13.
I am going to expand the scenario to 15. I do not want to write 15 expression, any other ways to do this?
That's definitely a good solution for this sample simplified problem. But it does not solve my problem. I simplified the problem I am facing right now. But in reality, it is much difficult. Can you answer my question directly like how to extract the value from the variable? That's what I really want to know.
I think it is not good practice to hardcode the variables, because the number of scenarios are dynamic, everytime if new scenario is added you need to add variables manually. I think the solution provided by me is flexible check with your original data.
I don't know of any way to do an indirect reference to the variable in your example. However, you could generate a pick() expression in a variable that would return the correct result per Scenario. Do you generate the vBest_Sales_* variables in a script loop? I can show you how to generate the pick() expression but it almost seems easier to generate the values into a table. Any reason you're not creating these values as table fields?
