See next three months, next year

[JesusAlv4]

see next three months, example, I select December 2019 and show values ​​2020 January and February

I use :   =Sum({<Month ={$(=Max(Month + 1))}>} MQTYORDERED)

It only shows the year 2019, but does not advance to the next year January 2020

[zhadrakas]

Hello,

max(Month+1) will not work, cause from 12 it will go to 13 and not (as it should) to 1.

You should use something like this:

Date based:

Month(AddMonths(max(Date),-1)) //last Month
Month(AddMonths(max(Date),0)) //current Month
Month(AddMonths(max(Date),1)) //next Month
Month(AddMonths(max(Date),2)) //next next Month

Note that you maybe need to add a condition for year in your set analysis if you have more years of data.

 regards

tim

[JesusAlv4]

It's my original qvw, I hope you can support because it doesn't work for me.

=Sum({<Month= {$(=AddMonths(Max(Date),0))}>} MQTYORDERED)

Add the sample original file qvw

Thank you for your help.

[zhadrakas]

try this expression:

=Sum({<Month= {$(=subfield(AddMonths(Max(MDELIVERYDATE),0),'/',2))}>} MQTYORDERED)

 maybe you want to add the year to the expression

=Sum({<Month= {$(=subfield(AddMonths(Max(MDELIVERYDATE),0),'/',2))}, Year ={$(=subfield(AddMonths(Max(MDELIVERYDATE),0),'/',3))} >} MQTYORDERED)

regards

tim 

[Brett_Bleess]

Jesus, did Tim's last post get you what you needed for your use case?  If so, do not forget to return to your post and use the Accept as Solution button on Tim's post to give him credit for the assistance and let other Community Members know that worked for the use case.  If you still have further questions, leave an update.

Regards,
Brett

[Prashant_Naik]

Hi,

this might help you

You need to create a tilldate flag in script and add that flag in you set analysis script

if(datefield<=today(),1,0) as tilldateflag write this flag in script

Set Analysis script : 

• sum(MonthYear={"$(=Date(Max({<tilldate={1}>}MonthYear),'MM-YYYY'))"}>}MQTYORDERED) //it will give you data for dec 2019
• sum(MonthYear={"$(=Date(AddMonths(Max({<tilldate={1}>}MonthYear),'MM-YYYY'),1))"}>}MQTYORDERED) //it will give you data for Jan 2020
• sum(MonthYear={"$(=Date(AddMonths(Max({<tilldate={1}>}MonthYear),'MM-YYYY'),2))"}>}MQTYORDERED) //it will give you data for Feb 2020

I have considered the fields that you have included in the app for reference.

Regards,

Prashant

[JesusAlv4]

Sorry, the sentence still does not do what I need, but I will try the solution that the user : Prashant

Regards.

[JesusAlv4]

Hi Prashant,

I already made the code as you say, month year, creation of the flag, but I have no result.
the current month is correct, but the following months do not work. 

I was creating a "NumericDate" field to do the operation subtracting the Monthyear, but it doesn't work.

QVW file attachment. 

Example:

Regards, 

Thank you for your help.

[tresB · Accepted solution]

Try expression like below:

sum({<Year,Month,MonthYear={"$(=Date(AddMonths(Max(MonthYear), 1), 'MMM-YY'))"}>}INVENTQTY)

[JesusAlv4]

Hello Tresesco,

Thank you very much, its correct, I see results now .

Regards,

Jesus