Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
thanhphongle
Contributor

expression as variable

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.


13 Replies
vvvvvvizard
Contributor III

Re: expression as variable

thanhphongle
Contributor

Re: expression as variable

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?

Employee
Employee

Re: expression as variable

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

thanhphongle
Contributor

Re: expression as variable

"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?

thanhphongle
Contributor

Re: expression as variable

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

Employee
Employee

Re: expression as variable

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])

or

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

or

  Sum(avg_PV_Category * [Selling prices])

HIC

thanhphongle
Contributor

Re: expression as variable

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

or

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

or

  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?

Employee
Employee

Re: expression as variable

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

thanhphongle
Contributor

Re: expression as variable

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.

Community Browser