Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Flag for next 12 months

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

Tags (3)
1 Solution

Accepted Solutions

Re: Flag for next 12 months

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


talk is cheap, supply exceeds demand
4 Replies

Re: Flag for next 12 months

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...;


talk is cheap, supply exceeds demand
Not applicable

Re: Re: Flag for next 12 months

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.

Re: Flag for next 12 months

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


talk is cheap, supply exceeds demand
Not applicable

Re: Flag for next 12 months

Nice! This does the job.

Thank you so much for fast and accurate advise!

Freddy

Community Browser