topic problem with QlikView variables!!! in QlikView

problem with QlikView variables!!!

ali_hijazi — Wed, 24 Aug 2011 09:59:28 GMT

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!!!

Re: problem with QlikView variables!!!

Miguel_Angel_Baeyens — Wed, 24 Aug 2011 10:15:06 GMT

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

problem with QlikView variables!!!

ali_hijazi — Wed, 24 Aug 2011 12:32:21 GMT

Oh no this gave me far different numbers

problem with QlikView variables!!!

kaushiknsolanki — Wed, 24 Aug 2011 12:34:47 GMT

Hi,

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

Regards,

Kaushik Solanki

problem with QlikView variables!!!

ali_hijazi — Wed, 24 Aug 2011 12:42:08 GMT

vStartYear is defined as follows:

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

and vPeriodDate is defined as follows:

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

Re: problem with QlikView variables!!!

Miguel_Angel_Baeyens — Wed, 24 Aug 2011 16:02:06 GMT

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