topic Variables in QlikView

Variables

Tue, 22 Mar 2016 09:01:56 GMT

Hi All

I have my dimension as [year week] and would like my objects or expressions to always give me 3 maximum year weeks.

I had created a variable called vmaxPeriod_3 calculated as max(YearWeek)-2 but sometimes when my max(YearWeek) is for example 201601, my vmaxPeriod_3 becomes 201599 when it should be 201511. How do l control this?

Please assist.

Regards,

Moses

Re: Variables

swuehl — Tue, 22 Mar 2016 11:47:46 GMT

You could create another field in your model (assuming YearMonth):

Year*12 + Month as YearMonthNum,

Then you can use this field to make your calculations across year bounderies.

Or create your YearMonth values as duals with underlying numeric date values and use AddMonths() to work out the YearMonth three months back.

Re: Variables

Gysbert_Wassenaar — Tue, 22 Mar 2016 11:48:18 GMT

Create a field that can be used for such calculations. num(MakeDate(Year,Week)) as numYearWeek. Then you can use the Max function on that and use multiples of 7 to get earlier values: max(numYearWeek)-7 for the previous week.

Re: Variables

adinning — Tue, 22 Mar 2016 12:37:55 GMT

You could also use the addmonths function.

Addmonths(YearMonth, -2)

This will however return the date as default, ie DD/MM/YYYY But should give the expected functionality.