problem with QlikView variables!!!

[ali_hijazi]

Hello I got a bar chart with the following expressions:

sum(

     {

     <PeriodDate = {"<=$(=vPeriodDate) >=$(=vStartYear)>

     }

     amount)/1500

on a text object the =$(=vPeriodDate) displays the value 40760

on another text object the =$(=vStartYear) displays the value 40575

these values are correct

the expression returns wrong values on the bar chart

However if I write the expression as follows the values are correct

sum(

     {

     <PeriodDate = {"<=40760 >= 40575"}

     }

     amount)/1500

I get the correct values

Please advise!!!

[Miguel_Angel_Baeyens]

Hi,

Try instead

sum(
     {
     < PeriodDate = {">=$(#vStartYear)<=$(#vPeriodDate)">
     }
     amount)/1500

The $(#variable) forces to return a numeric value, when possible.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[ali_hijazi]

Oh no this gave me far different numbers

[kaushiknsolanki]

Hi,

   Please tell us what does variable contains. and how you are getting it.

Regards,

Kaushik Solanki

[ali_hijazi]

vStartYear is defined as follows:

= Num(YearStart(PeriodDate,0,2))

and vPeriodDate is defined as follows:

if(PeriodDate = Today(), date(PeriodDate -1),PeriodDate)

[Miguel_Angel_Baeyens]

So,

vStartYear is actually a number while vPeriodDate is not, or at least, not always, since Date() function returns a string. Both must be in the same format PeriodDate is (I assume is a number since it's being compared to Today()). There are a lot of posts in this regard in the forums, but I'd recommend to properly format them in the script (so all they have the same format, numeric preferable).

Create a text object and test your expressions there, but for the time being this should do instead of your current vPeriodDate

If(PeriodDate = Today(), Num(Date(PeriodDate -1)), PeriodDate)

Now both variables and PeriodDate field will return a number (in the QlikView date format, kind of "40779") that can be used in the set analysis and should work as expected using the expression in my previous post.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica