Set analysis for scrollin previuos year and three month

[NickP_DF]

Hello guys,

I need to extract the bought quantity for the last year in the current month and in the further two (now it should be may/jun/jul 2021)

I've set the formula for the previous year, but I need some help with the second condition:

=Num(Aggr(Sum({<AnnoCompetenza={"$(=Year(Today())-1)"}>} QtaAcquistata), CodArticolo),'#.##0') 

Could someone help me, pls?

Thanks.

N. 

[edwin]

use addmonths and addyears,  you can use actual date instead of testing the year, or if you have the month

addyears(monthend(Today())-1) is your end date

monthstart(addmonths( addyears(monthend(Today())-1),-2))

or if you are using start of the month as your month field:

addyears(monthstart(Today())-1) is your end date

addmonths( addyears(monthmonthstart(Today())-1),-2)

[edwin]

check this out:
https://community.qlik.com/t5/Qlik-Design-Blog/AddYears-and-AddMonths-Functions/ba-p/1475141

[NickP_DF]

Thank you for the explanation, Edwin...sorry, but I wasn't clear enough in my question: I already have the availability of the reference year (AnnoCompetenza) and the reference Month (MeseCompetenza), but I'm not able to get the correct syntax for the set analysis to sum the Qty (QtaAcquistata) for the current month and the further two in the previous year (in this moment may/jun/jul 2021, but it has to roll, of course) for each article.

Hope now it's much clear now.

Thanks.

N.

[edwin]

assuming your month field MeseCompetenza is start of the month, like 1/1/2022,

your set analysis could be:

{<MeseCompetenza={">=$(monthstart(addmonths( addyears(monthend(Today())-1),-2)) )<=$(=addyears(monthstart(Today())-1))"}  >}

[edwin]

thats what i meant by using start and end dates in your set analysis

[NickP_DF]

Thank you, Edwin...sorry but your syntax seems to be incorrect (and I'm not able to understand where is the error).

P.S.: Your assumption that MeseCompetenza starts from the first month day and ends at the last one is correct. 

For example:

Year      Month  Quantita

2021       5              210

2021       6              220

2021       7              230

2021       8              240

In this case, until May, 31st 2022 I have to show 210+220+230=360; from June 1st 2022 I have to show 220+230+240=390

Hope it's clear.

Thanks.

N.

[edwin]

i would think that there is something else going on, Qlik is saying it doesnt recognize the fieldnames, eithere there is some other text outside the window or the field names are not correctly (case wise).  copy paste the bottom of the editor where Qlik interprets the expression.  pay attention to the format of the evaluated $()

[edwin]

i was missing a comma.  also you dont need the first monthstart:

=sum({<Dt={">=$(=addmonths(addyears(monthstart(Today()),-1),-2))<=$(=addyears(monthend(Today()),-1))"}>}volume)

[edwin]

this is obviously Qlik Sense but the same is seen in QlikView: