Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
StuartJohnson
Contributor
Contributor

Working with YearName in expressions

Hi All,

 

I'm trying to populate a pivot table with sales data for this and last financial years. Currently i can do that for this year using the following expression: =sum({<[FYear] = {"=YearName(Today())"}>}[Units]).

however if i try and do the same for last year using the expression: =sum({<[FYear] = {"=YearName(addyears(Today(),-1))"}>}[Units]), i get the same data for both columns.

 

Can someone explain where/why i'm going wrong please.

1 Solution

Accepted Solutions
sunny_talwar

Awesome, then try these

=Sum({<[FYear] = {"$(=YearName(Today(), 0, $(vFiscalYearStartMonth)))"}>} [Units])
=Sum({<[FYear] = {"$(=YearName(AddYears(Today(), -1), 0, $(vFiscalYearStartMonth)))"}>} [Units])

View solution in original post

20 Replies
pradosh_thakur
Master II
Master II

can you try this

=sum({<[FYear] = {"$(=YearName(addyears(Today(),-1)))"}>}[Units])
Learning never stops.
StuartJohnson
Contributor
Contributor
Author

Thanks i tried that, but it doesn't show any sales when i use that expression

sunny_talwar

How do you create FYear in the script? Is it created using Year function? If it is, then try this

=Sum({<[FYear] = {"$(=Year(AddYears(Today(), -1)))"}>} [Units])
StuartJohnson
Contributor
Contributor
Author

I created FYear using the below;

Yearname(TempDate2, 0, $(vFiscalYearStartMonth)) as FYear it creates a valur in the format 2018-2019. If i use the string it returns 0 for sales, if i use your formula it still returns 0 sales. But i can get it to work for this year?

sunny_talwar

How about this then

=Sum({<[FYear] = {"$(=YearName(Today(), 0, $(vFiscalYearStartMonth)))"}>} [Units])
StuartJohnson
Contributor
Contributor
Author

My variables are stored in my load tabs, can they still be used on sheets. However this still returns 0?

sunny_talwar

Can you check what do you get when you use this in a text box object

=YearName(Today(), 0, $(vFiscalYearStartMonth))
sunny_talwar

Also, check when you use this

=YearName(AddYears(Today(), -1), 0, $(vFiscalYearStartMonth))
StuartJohnson
Contributor
Contributor
Author

I get 2018-2019