0

Select Current Year in a Discovery

Trying to create a discovery to show only sales in the current year but I can't find a way to do it! This discovery will be used as a publication, so I always want it to just show the current year, without the user having  to select from a drop-down

I'd normally use a parameter to do this and select the last year, but I can't do that here because there is an invoice in our system that's dated in 2030!

6 replies

null
    • Senior Director of Product
    • Ian_Macdonald
    • 2 yrs ago
    • Reported - view

    Hi Sophie,

    Can you confirm the database type you are accessing? Dates are one of those things that behave differently analytically between SQL (IMDB and other relational DBs) and MDX (SSAS and SAP BW).

    Thanks,

    Ian

      • Sophie_durrant
      • 2 yrs ago
      • Reported - view

      Ian Macdonald it's an IMDB with a sql source

      • Sophie_durrant
      • 2 yrs ago
      • Reported - view

      Ian Macdonald and I'm on version 2020.18.055

    • Senior Director of Product
    • Ian_Macdonald
    • 2 yrs ago
    • Reported - view

    Hi Sophie,

    You could create a list that contains the last but one year, by creating a standard list of all the years, then filtering that to get the last two, then filtering that to get the first one. Then use that list in  a Slicer.

    The above shows an initial list of all the years, then filtered as described. you can see the output is the last but one year.

    Alternatively, you could get the system date, format it as Year in the same way as the members in your Year hierarchy, then match it into your Year hierarchy to get the current year member, again save as a list, then use that list in the slicer:

    StrToSet([Calendar].[Cal date year],DateFormat(Date(),"yyyy"))

    Hope that helps.

    Ian

    • Senior Director of Product
    • Ian_Macdonald
    • 2 yrs ago
    • Reported - view

    Hi Sophie,

    Further to the above, note that there is a subtle difference between "YYYY" and "yyyy" when formatting a date to a year.

    "yyyy" gives the Calendar Year, while "YYYY" gives the year of the week.

    See this article for further information:

    https://dangoldin.com/2019/01/06/javas-simpledateformat-yyyy-vs-yyyy/ 

    Ian

      • Sophie_durrant
      • 2 yrs ago
      • Reported - view

      Ian Macdonald thank you for your help- I managed to get what I was after using your second suggestion. I was not aware of the difference in yyyy and YYYY so thank you for pointing this out!

Content aside

  • Status Answered
  • 2 yrs agoLast active
  • 6Replies
  • 45Views
  • 3 Following