topic Re: expression as variable in QlikView

expression as variable

thanhphongle — Wed, 24 Jun 2015 10:36:00 GMT

Hello Community,

these are my tables which I load in my script

MAP_PV:

Mapping LOAD

Category,

avg_PV_for_Category

FROM

[B-Basisdaten.xlsx]

(ooxml, embedded labels, table is Kategorien);

MAP_Zins:

Mapping LOAD

Periode,

Zins

FROM

[B-Basisdaten.xlsx]

(ooxml, embedded labels, table is Zinsdaten);

Data:

LOAD *,

([Payment (Asset) p. m.]/[Selling price])*100 as Leasingfaktor, //created field Leasingfaktor

Applymap('MAP_PV', Category) as avg_PV_for_Category,

Applymap('MAP_Zins',[Lease start date]) as Zins;

Directory;

LOAD [Lease No] as Kaufschein,

Position,

Subposition,

Manufacturer as Hersteller,

[Serial Number],

Category,

Model as Anlagebezeichnung,

[Lease start date],

[Scheduled lease end date],

[Actual lease end],

[Cost centre (asset)] as Kostenstelle,

[Location of installation] as Standort,

[Selling price],

[Payment (Asset) p. m.],

Currency,

Leasinggeber as Region,

Technologie as Kategorie

FROM

[A-Import_LPM.xlsx]

(ooxml, embedded labels, table is Sheet1)

where [Payment (Asset) p. m.]>0;

let vAuswertungsdatum = today();

Join (Data)

LOAD *,

If(AvgLeasingfaktor>=1.5 and AvgLeasingfaktor < 2.0, '60 Monate',

If(AvgLeasingfaktor>=2.0 and AvgLeasingfaktor < 2.4, '48 Monate',

If(AvgLeasingfaktor >=2.4, '36 Monate','Festkontraktierte Verlängerung')))as Zuordnung;

LOAD Category,

Zins,

Avg(Leasingfaktor) as AvgLeasingfaktor

Resident Data

Group By Category,Zins;

What I actually want to do is to operate serveral results of an expression. For example:

If I have two different charts with different results

sum([Selling price]) = 1000 = I call the expression "Investment" -> 1. Chart

avg_PV_for_Category*sum([Selling price]) = 10000 = I call the expression "PV Kosten" -> 2. Chart

What I'd like to do is

Investment+PVKosten=11000

I know that I have to define those expression as variables.

So I have to go to: Settings/Variables

I put in the field "Value" sum([Selling price]) and give it the name "vInvestment"

analog avg_PV_Category*sum([Selling price]) and give it the name "vPVKosten"

Now I create a straight table and want to operate (sum,multiply, substract etc). But I dont really know the syntax of how to put them in an expression.

Nothing happens if i just add the expression vInvestment+vPVKosten

It returns me nothing.

So my questions are:

is the syntax correct if I define a new variable or do I have to add a "=" before the expression in the Valuefield?

-> =sum([Selling price]) -> vInvestment

what is the syntax to operate different expressions which are defined as variables?

-> vInvestment+vPVKosten ? (definetly this is the wrong syntax)

And is it possible to use variables in variables?

example.

I defined my variable sum([Selling price]) as vInvestment

I'd like to redefine my variable vPVKosten to avg_PV_Category*vInvestment

here I used my variable which I defined before.

I m a beginner in QV it would be great if you exlpain or give me solutions as comprehensive as possible.

Re: expression as variable

vvvvvvizard — Wed, 24 Jun 2015 11:08:52 GMT

The Little Equals Sign

Re: expression as variable

thanhphongle — Wed, 24 Jun 2015 12:24:18 GMT

I followed the instruction of "The Little Equals Sign"

I ceated the variables

vInvestitionsvolumen -> =sum([Selling prices])

vPVKosten -> =avg_PV_Category*$(vInvestionsvolumen)

I created a straight table without any dimension

I created an expression called "New Cost"

-> $(vInvestitionsvolumen)+$(vPVKosten)

it returns me nothing.

where is my mistake?

Re: expression as variable

hic — Wed, 24 Jun 2015 12:32:22 GMT

Most likely because you have naked field reference in avg_PV_Category (an unaggregated field) and this returns NULL. See Use Aggregation Functions!

I would advice you to skip the variables - at least for the moment - and focus of your formula. Once you have a valid formula, it should be easy to put this in a variable.

HIC

Re: expression as variable

thanhphongle — Wed, 24 Jun 2015 12:49:26 GMT

"I would advice you to skip the variables - at least for the moment - and focus of your formula. Once you have a valid formula, it should be easy to put this in a variable."

What do you exactly mean with valid formula?. My Qlikview knowledge is not that good. Could you give me an example as explanation?

Re: expression as variable

thanhphongle — Wed, 24 Jun 2015 13:01:58 GMT

and I even have the problem to return as expression my variable vInvestitionsvoumen.

I recently asked for help in the forum how to give out my variable PVKosten. Someone had this solution:

Create a straight table with no dimensions, create an expression.

(sum(aggr($(vPVKosten),Kaufschein,)))

and it really works

But I tried the same with my variable vInvestitionsvolumen

(sum(aggr($(vInvestitionsvolumen),Kaufschein,)))

it returns me nothing

Re: expression as variable

hic — Wed, 24 Jun 2015 13:06:33 GMT

It seems as if you want to calculate something like

avg_PV_Category * sum([Selling prices])

Well, then you should create a pivot table with this as expression. You should also add a second expression using just sum([Selling prices]) as a test. Further, you should also add some dimension (Month, Category, Region, or some other field).

Probably your first expression will not work since you use a naked field reference. So you need to figure out what to use instead:

Avg(avg_PV_Category) * sum([Selling prices])

Min(avg_PV_Category) * sum([Selling prices])

Sum(avg_PV_Category * [Selling prices])

HIC

Re: expression as variable

thanhphongle — Wed, 24 Jun 2015 13:38:38 GMT

Avg(avg_PV_Category) * sum([Selling prices])

Min(avg_PV_Category) * sum([Selling prices])

Sum(avg_PV_Category * [Selling prices])

That works fine if I type it down in a chart as expression.

But if i define expressions as variable and try to operate them in a chart. It does not work.

I attached a qvw file

In this file i created the variable vGewZins, vPVKosten, vInvestionsvolumen

With the solution of others QV returns vPVKosten if i use this as expression (sum(aggr($(vPVKosten),Kaufschein,)))

but if I only type down $(vPVKosten) it returns me 0. same for the other variables. !I m not sure if I really understood what you have been trying to tell me. But is it possible if you could have a look at my qvw file and make the changes directly so I can see the difference what I have done wrong so far?

Re: expression as variable

hic — Wed, 24 Jun 2015 14:05:20 GMT

Problem 1:

You use a variable "vInvVolumen" which is not defined.

Problem 2:

You have an expression "Sum($(vInvestitionsvolumen))", but the "vInvestitionsvolumen" is defined as "=sum([selling price])". Hence, you have an aggregation function inside another aggregation function. This is not possible.

Problem 3:

You have an Aggr() with a missing third parameter: "aggr($(vPVKosten),Kaufschein,)". This could cause problems.

Problem 4:

The variable "vInvestitionsvolumen" is defined as "=sum([selling price])", which means that it is calculated globally outside the chart. Then it is not sensible to use it inside the chart.

HIC

Re: expression as variable

thanhphongle — Wed, 24 Jun 2015 14:33:17 GMT

Problem1:

I created a variable called "vInvestitionsvolumen" and put in as Value sum([Selling Price])

isnt it correct like this ?

Problem 2:

so I remove the aggregation function and leave it only to vInvestitionsvolumen.

but how can i return "vInvestitionsvolumen" in a straight chart? I dont know the correct syntax.

Problem 3:

which third parameter is missing ?

Problem 4:

Someone told me to create the variable globally in order to operate with them. I attached a new qvw File called "TEST" My intention was to multiply the result of "durchschn. Kosten" from straight table 3 with "gew. Zins" from straight table 1. and after i want to substract "Gesamtkosten zzgl ZV" from the result before. but somehow i still cant manage it.

At the moment you are pointing on my mistakes which I really appreciate. but is it possible if u attach me a solution of my problem? I am a QV beginner. I m really sorry if I bother you with that.

Re: expression as variable

hic — Wed, 24 Jun 2015 15:27:42 GMT

There are way too many dependencies for my liking... You just need to go through your formulas and find the errors. I have started in the attached app.

A suggestion: Don't put labels on the expressions until they work. In the image below, you can see that there is an error in the 5th expression - so you need to fix that variable.

HIC

Re: expression as variable

thanhphongle — Wed, 24 Jun 2015 15:37:52 GMT

I cant open the png file. it says that i dont have the permission

Re: expression as variable

hic — Wed, 24 Jun 2015 18:50:26 GMT

Try the attached qvw file

Re: expression as variable

thanhphongle — Fri, 26 Jun 2015 10:23:25 GMT

thank you very much for your help. I gonna check this out on monday. I m currently not in the office.