topic Last year quarter in QlikView

Last year quarter

NickP_DF — Wed, 30 Nov 2022 11:25:40 GMT

Hello guys,

I need to calculate the selling summarize of the relative quarter of the last year; for example in September 2022 I had to consider Sep-Oct-Nov 2021, in October 2022 Oct-Nov-Dec 2021

To do this calculation I use this formula:

Sum({<AnnoCompetenzaVEN={"$(=Year(Today())-1)"}, MeseCompetenzaVEN={">=$(=Num(Month(Today())))<=$(=Num(Month(Today()))+2)"}>} QtaVendutaStorica)

My problem is that in November 2022 I should consider Nov-Dec 2021-Jan 2022, in December 2022 Dec 2021-Jan-Feb 2022

Could anyone help me with this set analysis formula, please?

Thanks.

Re: Last year quarter

Or — Wed, 30 Nov 2022 11:49:04 GMT

What you're describing isn't a quarter per se, near as I can tell, it's a running-three-months calculation. Perhaps try using Date = {">=$(=AddYears(MonthStart(Today()),-1)<=$(=AddYears(MonthEnd(AddMonths(Today(),2),-1)))"}>} or something similar (I hope I got the syntax spot on, since I can't test it and there's a lot of parenthesis in there).

Re: Last year quarter

NickP_DF — Fri, 02 Dec 2022 09:34:08 GMT

Hi,

Your suggestion is a good solution; I wasn't able to solve with the elegant set_analysis

Sum({<DataCompetenzaVEN={">=$(=AddYears(MonthStart(Today()),-1)<=$(=AddYears(MonthEnd(AddMonths(Today(),2)),-1))"}>} QtaVendutaStorica)

but I made it with a rough formula:

Sum(IF(DataCompetenzaVEN>=AddYears(MonthStart(Today()),-1) and DataCompetenzaVEN<=AddYears(MonthEnd(AddMonths(Today(),2)),-1),QtaVendutaStorica))

Are you able to teach me what is wrong in the first formula, pls?

Thank you.