Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I need some help with a problem that I would like to explain by the following example:
I've created several variables with each of them containing a set Expression:
vSales = SUM({<Type={1}>} PriceSale)
vCostOfSales = SUM({<Satzart={1}>} PricePurchase)
vWastage = SUM({<Satzart={2}>} PricePurchase)
vGrossProfit = SUM({<Type={1}>} PriceSale) - SUM({<Satzart={1, 2}>} PricePurchase)
I want to create a report showing 4 rows and value columns for several years. The rows should be:
Sales
Cost of Sales
Wastage
Gross Profit
For that, I created an Excel-table and uploaded it into my application:
Sort Position Variable
1 Sales vSales
2 Cost of Sales vCostOfSales
3 Wastage vWastage
4 Gross Profit vGrossProfit
After loading the table into QlikView, I created a cross table and set "Position" as dimension. When I use the field "Variable" as formula, QlikView shows the different variable names as text expression in each line, exactly as in the table above.
How can I make QlikView recognizing that it should present me the value of the variable that is named exactly as the field value is?
Thanks in advance!
Interesting, you could try this method where the variable is translated into a field value. you can even drop the variables if they're not needed elsewhere ...
Load * inline [
Item, Sales
A, 100
B, 50
A, 25
C, 15
B, 13
];
Set vSales = sum(Sales);
Set vItems = count(distinct Item);
Set vAvgSales = avg({<$1>} Sales);
//IDs must be consecutive and unique
Metrics:
Load * inline [
ID, Position, Variable, Expr
1, Total Sales, vSales, $(vSales)
2, Items, vItems, $(vItems)
3, Sales Avg of A, vAvgSales, $(vAvgSales(Item={'A'}))
4, Sales Avg of B, vAvgSales, $(vAvgSales(Item={'B'}))
];
ExprList:
Load concat(distinct Expr, ',', ID) as ExprList resident Metrics;
Let ExprList = peek('ExprList');
Drop Table ExprList;
Then create a chart with Position or ID as dimension and this as the expression ...
pick(ID,$(ExprList))
Not sure if this is what you are trying to achieve and to keep the pick function working the IDs must start at 1 and be unique and consecutive with no gaps.
flipside
Hi Dieter,
it would be easier if we could see your actually QVW, could you upload this please?
Andy
Even if you get the value of the variable shown, you won't get QV to evaluate the value of the variable as expression.
I believe you need to create a single expression that switches to the correct expression based on your dimension value, you can do this using a pick / match combination.
See some examples here:
Hi Dieter,
I'll leave to one side the fact that I think your approach is flawed and go for the obvious first: are you making sure to $ expand your variables in the expression? Putting vSales in the expression will just show the text representation of your expression. To make it execute, you need to wrap it in $(), eg $(vSales).
Try that first, and if you have no joy, we can delve into a better way to manage your expressions using variables.
George
Interesting, you could try this method where the variable is translated into a field value. you can even drop the variables if they're not needed elsewhere ...
Load * inline [
Item, Sales
A, 100
B, 50
A, 25
C, 15
B, 13
];
Set vSales = sum(Sales);
Set vItems = count(distinct Item);
Set vAvgSales = avg({<$1>} Sales);
//IDs must be consecutive and unique
Metrics:
Load * inline [
ID, Position, Variable, Expr
1, Total Sales, vSales, $(vSales)
2, Items, vItems, $(vItems)
3, Sales Avg of A, vAvgSales, $(vAvgSales(Item={'A'}))
4, Sales Avg of B, vAvgSales, $(vAvgSales(Item={'B'}))
];
ExprList:
Load concat(distinct Expr, ',', ID) as ExprList resident Metrics;
Let ExprList = peek('ExprList');
Drop Table ExprList;
Then create a chart with Position or ID as dimension and this as the expression ...
pick(ID,$(ExprList))
Not sure if this is what you are trying to achieve and to keep the pick function working the IDs must start at 1 and be unique and consecutive with no gaps.
flipside
Thank you! It's not a very smart way regarding performance but it works.
This is very clever.
Thank you.
I now have a filter pane with 4 options.
When I choose an option, the Chart measure changes accordingly!.
This has been fed into the colour expression too for the pareto chart.