Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Using variable names in field

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!

1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

Re: Using variable names in field

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, ',', IDas 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

View solution in original post

6 Replies
Highlighted
Partner
Partner

Re: Using variable names in field

Hi Dieter,

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

Andy

Highlighted
MVP
MVP

Re: Using variable names in field

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

Highlighted
Luminary
Luminary

Re: Using variable names in field

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

Highlighted
Specialist II
Specialist II

Re: Using variable names in field

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, ',', IDas 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

View solution in original post

Highlighted
Not applicable

Re: Using variable names in field

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

Highlighted
Creator
Creator

Re: Using variable names in field

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.