Using variable names in field

[#1]

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!

[awhitfield]

Hi Dieter,

it would be easier if we could see your actually QVW, could you upload this please?

Andy

[swuehl]

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:

Re: Put the formula from field into dollar sign expansion - and calculate it in expression of a char...

Re: Using formulas from excel in the straight table

[gsbeaton]

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

[flipside · Accepted solution]

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

[#6]

Thank you! It's not a very smart way regarding performance but it works.

[colinodonnel]

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.