CODEWITHSUNDEEP
qlikview
Adnan
Adnan

Reputation: 2967

Variance column in QlikView

I need to create a "Variance" column in qlikview:

             2013         2014       Variance
Measure 1    100          110        10%
Measure 2    105          100        -4.8%
...

Can this be done in Qlikview with just one "Calculated dimension" column that says something like:

[Value for Column2]/[Value for Column 1] - 1

So that it works for any new measures I add in the table and regardless of what the column 1 and column 2 are?

EDIT: Sample Data:

 Year     Measure1    Measure2
 2012     9750        197
 2013     10000       200
 2014     11000       210
 2015     11500       215

I need the output to be structured as shown below with the Variance column as a calculation between 2 selected Year dimension values.

Upvotes: 1

Views: 4028

Answers (3)

The Budac
The Budac

Reputation: 1633

Here is how to do it using variables and expressions. This will create 2 new columns that will look like a new dimension but will actually be defined by the use of an if() statement.

First step in the script we need to create a dimension that has the Measures in it. this should not associate to any of the column already in the data. (UDR stands for User Defined Report). the dual just allows us to define a sort order that is not alphabetical.

UDR:
load dual(UDR,Sort) as Rows inline [
UDR, Sort
Measure1, 1
Measure2, 2];

the result should be something like this.

Ctrl+T

Next step is creating the 2 variables for the years.

I do this in the script, but you could use any method of variable creation.

set vMaxYear="=max(Year)";
set vMinYear="=min(Year)";

Now we use the field Rows as the dimension.

And we need to create an expression for the max selected year. Notice that the first if() is testing which rows of the UDR the expression is on and then the definition of the expression for that line is provided. The second if() is testing the year dimension against the variable vMaxYear which will change as selections are made. Min year is the same just replace vMaxYear with vMinYear.

if(Rows='Measure1',sum(if(Year=vMaxYear,Measure1)),
if(Rows='Measure2',sum(if(Year=vMaxYear,Measure2))))

Lastly we use the column() function to calculate the variance in the third expression.

column(1)/column(2)

To make the expression labels dynamic I simply add =vMinYear into the label.

The result is this table, that will respond to my selections in the year list box.

2013 vs 2014

2013 vs 2014

2014 vs 2015

2014 vs 2015

Upvotes: 0

The Budac
The Budac

Reputation: 1633

I am 99% certain there is no built in function to do it.

I've played with a few options I thought might work, but only this is proving of any use.

I've defined the 2 calculated dimensions as variables Year1 and Year2 which I change through an input box. Then the simple calculated dimension =[$(Year2)]-[$(Year1)] gives me a new dimension of the variances.

Variable Dimensions

This assumes the Measure is something that comes from the data, something like this, and now you just want to display it over varying years. I haven't considered what to do if the measures are all expressions.

Table of Results

Upvotes: 0

Rob Wunderlich
Rob Wunderlich

Reputation: 101

You can do this with the Column() function. 

Column(2) / Column(1)

The number refers to the Expression column -- first Expression is #1, etc. Dimension columns are not counted.

An alternative that is insensitive to column postion, you can use column labels in the expression. Assume expressions with labels of "Sales" & "Margin". The variance expression can be written as:

[Margin] / [Sales]

Upvotes: 3

Related Questions