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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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

View solution in original post

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

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
Author

Nice! This does the job.

Thank you so much for fast and accurate advise!

Freddy