13 Replies Latest reply: Jun 26, 2015 6:23 AM by Thanh Phong Le RSS

    expression as variable

    Thanh Phong Le

      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
          Thanh Phong Le

          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
            Thanh Phong Le

            "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
                Henric Cronström

                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

              • Re: expression as variable
                Thanh Phong Le

                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
                  Thanh Phong Le

                    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?

                    • Re: expression as variable
                      Henric Cronström

                      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
                      Thanh Phong Le

                      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
                          Henric Cronström

                          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

                           

                          Image1.png

                        • Re: expression as variable
                          Thanh Phong Le

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

                          • Re: expression as variable
                            Thanh Phong Le

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