Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
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
MVP & Luminary
MVP & Luminary

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
MVP & Luminary
MVP & Luminary

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.

MVP & Luminary
MVP & Luminary

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