Discussion Board for collaboration on QlikView Scripting.
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.
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?
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
"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?
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
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
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?
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
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.