topic Re: help me to use of variable in QlikView

help me to use of variable

laxmanqlik — Wed, 25 Nov 2020 16:16:04 GMT

i have a requirement that,

in table i have a year field with 2011,2012,2013,2014

now using a variable i need grab the last two values even if another value is added to it

i.e, if we add 2015

i need to get 2014 and 2015

if we add another one 2016

i need to get 2015 and 2016

last two values i have to get.

Re: help me to use of variable

Anonymous — Mon, 13 Jun 2016 08:16:38 GMT

Hi laxman,

If you need two variables to do it or if you want to make ranges:

vMaxYear = Max(Year)

vMinYear = Max({<Year = {"<$(vMaxYear)"}>}Year)

If you want it on same var to make selections with actions:

vMaxYear = Max(Year)

vYears = '('&Max(Year)&'|'&Max({<Year = {"<$(vMaxYear)"}>}Year)&')'

Regards

Re: help me to use of variable

Colin-Albert — Mon, 13 Jun 2016 08:23:48 GMT

Let vMaxY1 = max(Year) ;

Let vMaxY2 = (vMaxY1 - 1) ;

Re: help me to use of variable

Mark_Little — Mon, 13 Jun 2016 08:39:42 GMT

Hi,

Colin's answer should get them for you, But there are a few different ways you can do this.

If you could supply us some more information on what your are wanting to do with these years we could suggest the best approach.

i.e. Just display in a test box or feed into set analysis.

Mark

Re: help me to use of variable

Anonymous — Mon, 13 Jun 2016 09:27:23 GMT

Hi Colin,

What happen if there isn't two consecutive years?

Re: help me to use of variable

laxmanqlik — Mon, 13 Jun 2016 10:05:31 GMT

i tried but it showing

in valid dimesion

Re: help me to use of variable

laxmanqlik — Mon, 13 Jun 2016 10:06:50 GMT

hi mark

i want to display it in a textbox

Re: help me to use of variable

Mon, 13 Jun 2016 10:17:30 GMT

Hi ,

Colin's answer is precise but if you want the last two years for table having more than 1 distinct year , then use an additional 'if' statement for checking the count in the expression which Manuel has provided .

Re: help me to use of variable

Mark_Little — Mon, 13 Jun 2016 10:39:15 GMT

Hi,

One way would be

=' Selected - ' & MAX(GetFieldSelections(Year)) & '- Previous - ' & MAX(GetFieldSelections(Year )-1)

Mark

Re: help me to use of variable

Anonymous — Mon, 13 Jun 2016 10:43:22 GMT

Could you share an example app?

Re: help me to use of variable

pho3nix90 — Mon, 13 Jun 2016 10:57:12 GMT

I would suggest the following.

IN the script editor do the following resident load

Data:
LOAD * INLINE [
    YearField
    "2011,2012,2013,2014,2016"
];

We will assume the above is the data set for this example, from the above do a resident load

LOAD
YearField,
SubField(YearField,',') as FixedYearField
Resident Data;

It will create individual records for each year, and link back to the original text.

Now in the chart expression use the following:

Latest

Max(FixedYearField,1)

Second

Max(FixedYearField,2)

Just put the above in the variables you need

Re: help me to use of variable

pho3nix90 — Mon, 13 Jun 2016 11:09:31 GMT

Reading replies I might have misunderstood your explanation of the year. If it is just a normal year field then the below will do the trick, just replace FixedYearField with your field

Latest

Max(FixedYearField,1)

Second

Max(FixedYearField,2)

Or use this. set a variable with the below code

='Max(FixedYearField,$1)'

And you can now use a single variable to call records like below, for this example the variable is called vtest.

Now to call the latest year, do

$(vtest(1))

Second latest

$(vtest(2))

Re: help me to use of variable

susovan — Mon, 13 Jun 2016 11:38:29 GMT

Hi laxman,

Try this,