topic Re: Flag for next 12 months in QlikView

Flag for next 12 months

Thu, 04 Sep 2014 16:30:21 GMT

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

Re: Flag for next 12 months

Gysbert_Wassenaar — Fri, 05 Sep 2014 07:58:21 GMT

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

Re: Re: Flag for next 12 months

Fri, 05 Sep 2014 11:05:58 GMT

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

Gysbert_Wassenaar — Fri, 05 Sep 2014 11:26:11 GMT

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

Re: Flag for next 12 months

Fri, 05 Sep 2014 13:07:22 GMT

Nice! This does the job.

Thank you so much for fast and accurate advise!

Freddy