Show the value until a a specific value passed as variable

[pascos88]

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

[YoussefBelloum]

Hi,

Ok but how you want to show these values?

can you add more details ? show the expected output ?

[pascos88]

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

[YoussefBelloum]

what type of object are you using ?

[pascos88]

Pivot table, fields and a variable

[johanlindell · Accepted solution]

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.

[jonathandienst]

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,

[johanlindell]

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.