Specify the past month year_over_year?

[datanibbler]

Hi,

I'm thinking:

=> For most of my apps, I have several historical values to display. Usually, they are

   - average of last week

   - average of last month

Currently, I use a field defined in the master_calendar called "Monat_num" (a numeric value) to generate the month_before_this.

However, as the master_calendar runs over three years, in some cases I also have to specify the current year because there is data in the database for the years before this.

<=> How can I make this flexible so I won't have people calling all over the place on Jan2 because the historical values don't work anymore?

I hope I have described my problem precise enough. I'm looking for a way to specify the last month (the last week is not so critical, it won't work for one week) independent of the year. Is that possible?

Thanks a lot!

Best regards,

DataNibbler

[Gysbert_Wassenaar]

Create serial month and week fields that always increase by one and don't reset per year. For example like this:

autonumber(weekstart(MyDate),'weekserial' as _WeekSerial

autonumber(monthstart(MyDate),'monthserial' as _MonthSerial

You can use these new fields in the set analysis expressions for last week and last month averages instead of the month field.

If you can use a month field that includes the year you could also create a field for example like this:

date(monthstart(MyDate),'YYYYMM') as YearMonth

[datanibbler]

Hi Gysbert,

thanks! I have in the meantime found out a possible myself, but that is a very good idea as well, given that QlikView can handle numbers better than texts.

Right now, there are more pressing things to do, but I'll try to start implementing this before Christmas - else I'll have some repairing to do in the first days of next year...

Best regards,

DataNibbler