topic Re: Show the value until a a specific value passed as variable in QlikView

Show the value until a a specific value passed as variable

pascos88 — Thu, 08 Mar 2018 09:11:48 GMT

Hello,

I have to show the year until a specific one.

I already have all years and a variable passed by user.

Starting from 2018 if a variable is 3 I have to show 3 columns 2018 2017 2016 2015... so I have to go 3 years back

if vYears is 2 I gave to show column until 2016... 2018 2017 2016 an so on..

How I can use this variable in table?

Thanks for any helps

Re: Show the value until a a specific value passed as variable

YoussefBelloum — Thu, 08 Mar 2018 09:13:53 GMT

Hi,

Ok but how you want to show these values?

can you add more details ? show the expected output ?

Re: Show the value until a a specific value passed as variable

pascos88 — Thu, 08 Mar 2018 09:19:15 GMT

So I have a table :

if vYears = 2

Company 2018 2017 2016

a x y z

if v years = 3

Company 2018 2017 2016 2015

a x y z xy

the values are an max(Score). I have everything, I have just to limit the years based on the vYears variables.

Thanks a lot

Re: Show the value until a a specific value passed as variable

YoussefBelloum — Thu, 08 Mar 2018 09:31:25 GMT

what type of object are you using ?

Re: Show the value until a a specific value passed as variable

pascos88 — Thu, 08 Mar 2018 09:32:04 GMT

Pivot table, fields and a variable

Re: Show the value until a a specific value passed as variable

johanlindell — Thu, 08 Mar 2018 09:36:23 GMT

Put it in the formula and use set expression:

Sum ({<Year = {">=$(=(Max (Year) - $(vYears)))<=$(=(Max (Year)))"}>} Sales)

and have the Year in the dimesion of your table.

Re: Show the value until a a specific value passed as variable

jonathandienst — Thu, 08 Mar 2018 09:41:42 GMT

Do you have a master calendar? Does it have a Year field and is it linked to your facts/measurements?

If so, use a set expression like this in your expressions:

=Sum({<Year = {">=$(=Max(Year) - vYears)"}>} <metric expression> )

This will limit the chart/table to the number of years required. Using Max(Year) means that you can select another year and go back vYears from there as well as from the current year,

Re: Show the value until a a specific value passed as variable

johanlindell — Thu, 08 Mar 2018 10:18:51 GMT

Hi,

You need to add the part where Year also should be smaller or equal to Max(Year) as you are "overriding" the selection in the Year-field. Otherwise if you select 2016, the field selection in Year will be >=2014 (with vYears = 2) and it will show 2014-2018. Adding <=$(=(Max(Year)) will giv a Year-selection >=2014<=2016 and it will show 2014-2016.