Values of last year per row with year selection

[Mich90GSG]

Hey guys,

we are actually working on the following problem (see my example attached):

We are having Sales per Product / Month / Year.

For our forecasting we need the "Sales per product of last year" of the available months this year (e.g. if the products is now reported until Feb 2020 we need the sum of Jan-Feb 2019. This seem to work without selection.

=
	//Pre-Year same Months as Max Year
	sum( {<Year={"$(=max(Year)-1)"}>}
		Aggr( {<Year={"$(=max(Year)-1)"}>}
			If(Month <= max({<Year={"$(=max({1}Year))"} >} total <Product> Month) 
			, Sales)
		, Product, Year, Sales)
	)

Our problem is: If we now select 2020 the numbers of the year before are gone.

Could you give me a hint on how to fix that?

Thanks in advance!

[Mich90GSG]

Guys,

we are still working on this problem and got another idea which is like:

sum({<Year={"$(=max(Year)-1)"}, Month = p({<Year={"$(=max(Year))"}>}  Month)>} Sales)

Aparently, here the problem is that p() does not get the possible Months per product in that table. 

See attached exmaple.

Happy for any hint!

[Brett_Bleess]

Have a look at the following Design Blog posts, hopefully one of them may shed some light upon where you are going wrong, I struggle with Set Analysis myself, so apologies for not being more help:

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

I think that should cover things, those may have some additional related links in them too...

Regards,
Brett

[NealMason]

---

MyGroundBiz wrote:

Have a look at the following Design Blog posts, hopefully one of them may shed some light upon where you are going wrong, I struggle with Set Analysis myself, so apologies for not being more help:

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

I think that should cover things, those may have some additional related links in them too...

Regards,
Brett

---

Many thanks for that complete information!