-1

I have a page with a control filter witch is a list of years (I don't want to use a range period selector but allow users to click on year they want to analyse instead)

I am trying to set 4 scorecard in my GDS that show the value for the selected year, selected year-1, -3 and -maximum.

Here is my dataset :

Date | Value

31/12/2020 | 20

31/12/2019 | 10

31/12/2018 | 5

31/12/2017 | 2

30/09/2016 | 1

For example, in the date control list, if I choose 2019, I should have :

  • Score card °1 "Year" : value = 10 (corresponding to 2019)
  • Score card °2 "Year-1" : value = 5 (corresponding to 2018)
  • Score card °3 "Year-3" : value = 1 (corresponding to 2016)
  • Score card °4 "Year-max" : value = 1 (corresponding to 2016 since it is the older year)

Or for example, in date control list, if I choose 2020, I should have :

  • Score card °1 "Year" : value = 20 (corresponding to 2020)
  • Score card °2 "Year-1" : value = 10 (corresponding to 2019)
  • Score card °3 "Year-3" : value = 2 (corresponding to 2017)
  • Score card °4 "Year-max" : value = 1 (corresponding to 2016)

I have tried with filters, calculated fields, mixted data, etc... But never find a way to achieve this.

Does anyone have an idea ?

Thanks in advance!

Romain MALTRUD
  • 1
  • 2

1 Answers1

0
  • You need to create a parameter "year" with the type number.
  • Create a field "year differences", which is the year of the date minus the value of the parameter. So the formular should look like: YEAR(Date)-year
  • For the Score card 1 to 3, a filter has to be created on the field "year differences"
  • In "Score card °4" you ask for the oldest year. This cannot be done by a filter. Is it ok for you to display this value in a normal table? The set the rows per page to 1 and the order by date ascending.
Samuel
  • 2,923
  • 1
  • 4
  • 19