Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey,
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?
Thanks!!!!!!!
Hi,
Please find attached file for solution.
Regards,
Jagan.
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.
Still, thank you for providing another solution.
Hi,
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.
Regards,
Jagan.
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?
-Rob