5 Replies Latest reply: May 12, 2011 9:57 AM by Toni Kautto RSS

    Error in expression with variable

      Hello all,

       

      I have two variables, to get the year and month of the last date.

      The variables are defined like this:

      vLastMonth= Month(Max(DataEmissao)) , and vLastYear= Year(Max(DataEmissao))  // DataEmissao is a Date Field

       

      The variable are getting the right value, no problem on that.

       

      But I have an expression chart, that is like this:  sum({$<AnoEmissao={2011}, MesEmissao={May} >}  TotalItem)

       

      The expression also works, but every month I need to change it to the last month. I want to use the variable, but i dont know how.

      I tried this: sum({$<AnoEmissao={$(vLastYear)}, MesEmissao={$(vLastMonth)} >}  TotalItem)

      I also tried a lot of variations of that, but still no sucess!

       

      Please help me!

       

      Thanks

        • Re: Error in expression with variable
          Toni Kautto

          You probabaly need to format the variables, since the variable as such is a decimal value and not a "Year" or "Month" value.

           

          LastMonth= Month(Month#(Max(DataEmissao)))

          vLastYear= Year(Year#(Max(DataEmissao)))

           

          When working with dates always keep in mind that you are working with Dual values, where calculation is done on the numeric value and presentation with the  textual value. This is why you often need to use for example Date(Date#()) to alter both the numeric and textual value of the variable or field.

            • Re: Error in expression with variable

              Well, it did not work.

               

              Still, I dont know the exactly sintax of the expression when using variables.

               

              Thanks anyway.

                • Re: Error in expression with variable
                  Toni Kautto

                  Erich solution likley works very well, but in the long run you will be better of getting in control of and using the date/time functions that qlikview provide as these open up possibilites to do more efficient calculations with dates and timestamps.

                   

                  An other possibility is that your data is not properly formatted in the table, meaning that year for example is not a integer value but has decimal value as well. This is easier to evaluate if you can provide a smaple of your QVW.

                   

                  You could use num(vLastMonth) and num(vLastYear) to see the exact numeric value of the variables. Then follow it up by adding a list boxes with Num(AnoEmissao) and Num(MesEmissao) so that you can confirm that the values you are trying to apply really exist. This should also reveal to you if your table have solid integer, decimal och even textual values.

                • Re: Error in expression with variable
                  Erich Shiino

                  It's better if you compare date fields on set analysis using numbers.

                  On your script I would create another field called nMesEmissao

                   

                  The sintax will be

                  num(Month(DataEmissao))) as nMesEmissao

                   

                  Similarly,

                  vLastMonth= num( Month(Max(DataEmissao)) )

                  Thus,

                  Jan =1

                  Feb = 2 and so on...

                  This might solve your set analysis problem.

                   

                  Hope it helps,

                   

                  Erich