topic Re: Using variable names in field in QlikView

Using variable names in field

Fri, 06 Nov 2015 14:25:12 GMT

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!

Re: Using variable names in field

awhitfield — Fri, 06 Nov 2015 15:19:32 GMT

Hi Dieter,

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

Andy

Re: Using variable names in field

swuehl — Fri, 06 Nov 2015 15:30:13 GMT

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 chart

Re: Using formulas from excel in the straight table

Re: Using variable names in field

gsbeaton — Fri, 06 Nov 2015 15:31:18 GMT

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

Re: Using variable names in field

flipside — Fri, 06 Nov 2015 16:15:50 GMT

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

Re: Using variable names in field

Tue, 15 Dec 2015 16:59:05 GMT

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

Re: Using variable names in field

colinodonnel — Mon, 19 Mar 2018 16:31:38 GMT

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.