topic Re: Error in expression with variable in QlikView

Error in expression with variable

Thu, 12 May 2011 12:51:45 GMT

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

ToniKautto — Thu, 12 May 2011 12:56:58 GMT

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

Thu, 12 May 2011 13:20:38 GMT

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

erichshiino — Thu, 12 May 2011 13:25:52 GMT

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

Re: Error in expression with variable

Thu, 12 May 2011 13:51:52 GMT

Thanks Erich and Toni,

but after try a lot of expressions, I finally found the right way.

I forgot to use ".

So, the right expression is sum({$<AnoEmissao={"$(vUltAno)"}, MesEmissao={"$(vUltmes)"} >} TotalItem)

Thanks all

Rogério Faria

Re: Error in expression with variable

ToniKautto — Thu, 12 May 2011 13:57:22 GMT

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.