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
-
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
-
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
-
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