topic Re: Rolling Past 12 Months in QlikView

Rolling Past 12 Months

dseelam — Fri, 24 Jan 2014 19:07:20 GMT

Hello Every One

The below statement working perfectly alright but does the past 12 months will be changing daily once the script is reloaded ?

Let vMin = num(Makedate(2011,01,01));

Let vMax = num(today());

Temp:

Load

Date($(vMin) + Iterno() -1) as Date

Autogenerate 1

While Date($(vMin)) + Iterno() -1 <= $(vMax);

MasterCal:

Load Date,

Month(Date) as month,

Year(Date) as Year

Resident Temp;

Drop table Temp;

Re: Rolling Past 12 Months

Fri, 24 Jan 2014 19:15:50 GMT

Hey Dinesh,

Rolling past 12 months will always be changing as time progresses.

Say today past 12 months will be Jan 2013 - Jan 2014

When you are in Feb it should be Feb 2013 - Feb 2014

If you want to do that you can change vMin

Let vMin = num( Addmonths(Today(),-12)) ;

Hope it helps

Thanks

Re: Rolling Past 12 Months

dseelam — Fri, 24 Jan 2014 19:33:13 GMT

Hope it will change every day instead of every month

Re: Rolling Past 12 Months

aveeeeeee7en — Fri, 24 Jan 2014 19:35:27 GMT

Hi Dinesh

In the Script create this Field:

if(TempDate>=monthstart(AddMonths($(vToday), -12)) AND TempDate<monthstart($(vToday)), 1, 0) as Rolling12MthFlag

Create an variable vToday which has value of your Today Date or your Table's Max Date as per your requirement.

After creating the Above field in your Script now create a Chart.

eg. Line Chart - Your Dimension would be : Month_Year Field

Your Expression would be : sum({$ <Rolling12MthFlag={1}>} SalesAmount)

Hope this works for you.

Regards

Aviral Nag

Re: Rolling Past 12 Months

Fri, 24 Jan 2014 19:53:21 GMT

Sorry should have explained better. It will change everyday as we are subtracting 12 months from today.

so today it will be 01/24/2013 to 01/24/2014 and

tomorrow 01/25/2013 to 01/25/2014 and so on as long as the app gets reloaded daily.

Thanks

Re: Rolling Past 12 Months

Fri, 24 Jan 2014 23:10:39 GMT

Use MonthStart function if you need to reset the value to 1st of month