Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Re: Working with YearName in expressions

Awesome, then try these

=Sum({<[FYear] = {"$(=YearName(Today(), 0, $(vFiscalYearStartMonth)))"}>} [Units])
=Sum({<[FYear] = {"$(=YearName(AddYears(Today(), -1), 0, $(vFiscalYearStartMonth)))"}>} [Units])
20 Replies
pradosh_thakur
Honored Contributor II

Re: Working with YearName in expressions

can you try this

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

Re: Working with YearName in expressions

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

Re: Working with YearName in expressions

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

Re: Working with YearName in expressions

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?

Re: Working with YearName in expressions

How about this then

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

Re: Working with YearName in expressions

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

Re: Working with YearName in expressions

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

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

Re: Working with YearName in expressions

Also, check when you use this

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

Re: Working with YearName in expressions

I get 2018-2019