Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
NickP_DF
Creator
Creator

Set analysis for scrollin previuos year and three month

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. 

Labels (1)
2 Solutions

Accepted Solutions
NickP_DF
Creator
Creator
Author

Got it!

=Sum({<AnnoCompetenzaACQ={"$(=Year(Today())-1)"}, MeseCompetenzaACQ={">=$(=Num(Month(Today())))<=$(=Num(Month(Today()))+2)"}>} QtaAcquistataStorica)

 

Thank you for the support :-).

N.

View solution in original post

edwin
Master
Master

it appears your month is not a date but a number.  that would have been a great info to have.    i would suggest you use a real date as your solution is not scalable.  if this were a real date you wouldnt need to test for year.

good you solved your issue.

View solution in original post

14 Replies
edwin
Master
Master

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)

 

NickP_DF
Creator
Creator
Author

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
Master
Master

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
Master
Master

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

NickP_DF
Creator
Creator
Author

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
Master
Master

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
Master
Master

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_0-1653584882926.png

 

 

edwin
Master
Master

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

edwin_1-1653584986365.png