expression as variable

[thanhphongle]

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.

[vvvvvvizard]

The Little Equals Sign

[thanhphongle]

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?

[hic]

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]

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

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

[hic]

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]

  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?

[hic]

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]

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.