topic Re: Working with YearName in expressions in QlikView

Working with YearName in expressions

StuartJohnson — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Working with YearName in expressions

pradosh_thakur — Wed, 16 Jan 2019 11:29:09 GMT

can you try this

=sum({<[FYear] = {"$(=YearName(addyears(Today(),-1)))"}>}[Units])

Re: Working with YearName in expressions

StuartJohnson — Wed, 16 Jan 2019 12:04:19 GMT

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

Re: Working with YearName in expressions

sunny_talwar — Wed, 16 Jan 2019 12:31:07 GMT

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

Re: Working with YearName in expressions

StuartJohnson — Wed, 16 Jan 2019 12:44:11 GMT

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

sunny_talwar — Wed, 16 Jan 2019 12:47:48 GMT

How about this then

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

Re: Working with YearName in expressions

StuartJohnson — Wed, 16 Jan 2019 12:49:36 GMT

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

sunny_talwar — Wed, 16 Jan 2019 12:51:25 GMT

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

sunny_talwar — Wed, 16 Jan 2019 12:52:03 GMT

Also, check when you use this

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

Re: Working with YearName in expressions

StuartJohnson — Wed, 16 Jan 2019 12:52:53 GMT

I get 2018-2019

Re: Working with YearName in expressions

StuartJohnson — Wed, 16 Jan 2019 12:54:15 GMT

For the second expression i get the correct 2017-2018. i guess i must be doing something wrong elsewhere.

Re: Working with YearName in expressions

sunny_talwar — Wed, 16 Jan 2019 12:55:11 GMT

Now can you check when you use this

=Sum({<[FYear] = {"2018-2019"}>} [Units])

and

=Sum({<[FYear] = {"2017-2018"}>} [Units])

Re: Working with YearName in expressions

sunny_talwar — Wed, 16 Jan 2019 12:56:54 GMT

If these two worked... I am almost certain that these should work as well...

Current Year

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

Last Year

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

Re: Working with YearName in expressions

StuartJohnson — Wed, 16 Jan 2019 12:58:04 GMT

the first one 2018-2109 returns 0, the second 2017-2018 returns a value

Re: Working with YearName in expressions

sunny_talwar — Wed, 16 Jan 2019 13:00:33 GMT

Do you have data for 2018-2019? Also, make sure that there is not another selection which contradicts your set analysis?

Re: Working with YearName in expressions

StuartJohnson — Wed, 16 Jan 2019 13:00:37 GMT

Correction, they both return values.

Re: Working with YearName in expressions

StuartJohnson — Wed, 16 Jan 2019 13:01:51 GMT

Using your latest expressions i get sales for this year and -1 for all rows in Last year

Re: Working with YearName in expressions

sunny_talwar — Wed, 16 Jan 2019 13:02:33 GMT

Awesome, then try these

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

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

Re: Working with YearName in expressions

StuartJohnson — Wed, 16 Jan 2019 13:04:31 GMT

Thank you, so much. now they're both returning values. I dont suppose you could explain why what i was trying initially wouldn't work?

Re: Working with YearName in expressions

sunny_talwar — Wed, 16 Jan 2019 13:07:27 GMT

First, you were not using the dollar sign expansion in your variable... which does a row by row comparison... to compare FYear to a single value... you need a dollar sign expansion

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

Second, I think you probably will get different values if you use this is a text box

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

vs this

 =YearName(AddYears(Today(), -1))

Which is why I believe @pradosh_thakur's expression didn't work for you... otherwise his expression would have worked.