Flag for next 12 months

[#1]

Hi,

We have a forecast file containing weekly periods with a start and finish date.

The file contains historical data and future period data.

What I am trying to do is to set different flags on this file during load.

I would like to put a flag for Last month, next month and next 12 months but not sure how to do it as I have a start and finish interval per week.

Hope someone can help me on this.

See attached excel file for better description of the data model.

Best regards,

Freddy

[Gysbert_Wassenaar]

I'm not quite sure what you're after. But investigate the InMonth and InYear functions in the help file.

Perhaps you want something like:

LOAD *,

     InMonth(PeriodFinishDate,today(),-1) as FlagPrevMonth

     InMonth(PeriodFinishDate,today(),1) as FlagNextMonth

     InYear(PeriodFinishDate,today(),1) as FlagNext12Months

FROM ...excel_file...;

[#3]

Hi Gysbert,

Thanks a lot for your input. It is close. But as you can see in my attached file the next 12 months is really starting from next year. I would like to sum the forecast for next 12 months (or 52 weeks) from the weekly period starting after this weekly period.

[Gysbert_Wassenaar · Accepted solution]

Ok, try

LOAD *,

     (PeriodFinishDate < Today() and PeriodFinishDate >= addmonths(Today(),-1)) as FlagPrevMonth,

     (PeriodFinishDate > Today() and PeriodFinishDate <= addmonths(Today(),1)) as FlagNextMonth,

     (PeriodFinishDate > Today() and PeriodFinishDate <= addmonths(Today(),12)) as FlagNextYear    

FROM ...

[#5]

Nice! This does the job.

Thank you so much for fast and accurate advise!

Freddy