topic Re: Rolling Months in QlikView

Rolling Months

Tue, 07 Jun 2011 06:50:03 GMT

Hi!

Plz I need some help!!!

I have attached a sample app below which contains some data.

If I am looking at May 2010 data (i.e. 15 ) then this means that it is the data is uptill May (i.e. Apr and May).

Now i want a chart like the attached one.

Suppose i have Clicked the dimension Monthyear as 'Apr 2011' Then the value for this is calculated as: May2010+Jun 2010+......Apr2011.

My X axis will contain last 12 Month from the selected dimension Value.

Re: Rolling Months

Tue, 07 Jun 2011 07:57:42 GMT

Hi,

Have a look at the attached demo application which has been created by John Witherspoon

This application explains about the functionality of Rolling 12 months, Qtr and YTD in detail.

Hope this helps you.

- Sridhar

Rolling Months

Tue, 07 Jun 2011 08:50:19 GMT

Thanks for the help!

But I don't want this.

My actual problem is How to convert' Upto data' into 'for the Month data'.

if Uptill May 2010 data (i.e. 15 (April + may)) and April 2010 data is 10.

Then this means that April data is 10. May data is 5 (In month may Data is 5 only).

Rolling Months

pat_agen — Tue, 07 Jun 2011 09:12:16 GMT

hi,

You want to isolate each month's total from the running total in your original dataset. is that correct?

put this preceeding load before your load inline statement.

load

Monthyear,

Uptodata,

if(left(Monthyear,3)='Apr',Uptodata,Uptodata - previous(Uptodata)) as MonthData;

From your data it looks as if your accumulated total resets itself in April so we test for April.

MonthData shoudl give you what you require.

have I understood your request correctly?

Re: Rolling Months

rahulgupta — Tue, 07 Jun 2011 09:18:25 GMT

Hey Hi,

Kindly find the attached file.

Regards

Re: Rolling Months

Tue, 07 Jun 2011 09:24:13 GMT

Thanks a lot Pat & Rahul !!!!!.

Its working perfectly in the sample data which I have uploaded.

Hope it works in my Main application also.!!!

can u solve the second issue also.....The chart that I want to prepare.

Rolling Months

pat_agen — Tue, 07 Jun 2011 09:31:50 GMT

hi,

could you give some more clarification about your requirements.

Your initial post was not very clear and Sridhar gave you the reply I was thinking about. It wasn't till you replied to that mail that I understood you requirement. Same goes for the graph - I don't see what you are trying for.

Rolling Months

Tue, 07 Jun 2011 09:58:29 GMT

i have a code for what I want...

let me try myself....

Thanks to all for there help!!!

Rolling Months

Tue, 07 Jun 2011 11:19:29 GMT

i am attaching a chart.

May be now its clear.

Re: Rolling Months

Tue, 07 Jun 2011 11:20:09 GMT

i am attaching a chart.

May be now its clear.

Rolling Months

pat_agen — Tue, 07 Jun 2011 13:19:49 GMT

hi,

you are looking for a rolling twelve month figure for each month. Sridhar pointed you in a very good direction. The script will need adapting - your starting point is different and you want a RollingTwelveMonth instead of 3Month but all the mechanics are there. Basically you will need to create an AsOfCalendar which when linked to your fact table will allow you create your chart.

Try the document from John W. and see how far it gets you.

Re: Rolling Months

pat_agen — Wed, 08 Jun 2011 06:23:48 GMT

hi,

I don't know if you find your solution yet.

Attached is one version adapted from a John Witherspoon answer. sorry I don't have the link here. It is slightly different from the version posted by Sridhar but builds an AsOf Calendar as in that example.

I've adapted it to take your input data as the jump in point. Works perfectly (I haven't seen much of John W's stuff that doesn't to tell the truth! )

Anyway - hope it helps.

Rolling Months

Wed, 08 Jun 2011 09:31:59 GMT

actually I am not able to get 'for the month data'.

The previous func is not working.

For every monthYear I may have more than one value of Uptodata and the months are not sorted....therefore the previous is not working as I want.

Actually i am having data some what like this:

LOAD * INLINE [

Monthyear,Uptodata,Month

Apr 2010, 10,1

Apr 2010, 20,1

Jun 2010, 20,3

Jun 2010, 40,3

Jul 2010, 22,4

May 2010, 15,2

May 2010, 10,2

Sep 2010, 35,6

Nov 2010, 50,8

Nov 2010, 10,8

Jan 2011, 60,10

Feb 2011, 63,11

Aug 2010, 30,5

Apr 2011, 20,1

May 2011, 25,2

Oct 2010, 45,7

Mar 2011, 70,12

Jun 2011, 30,3

Dec 2011, 65,9

Aug 2011, 40,5

Sep 2011, 43,6

Dec 2010, 55,9

Oct 2011, 50,7

Jul 2011, 32,4

Nov 2011, 60,8

Jan 2012, 70,10

Feb 2012, 80,11

Mar 2012, 90,12

];

Re: Rolling Months

Wed, 08 Jun 2011 10:14:55 GMT

Check this attached application.

-Sridhar

Rolling Months

Wed, 08 Jun 2011 11:01:53 GMT

Thanks a lot Sridhar!!!

I am using somewhat like the same code.In dummy application Its working fine...

Let me check if its working in my Main application also.

Then i willcome back!!!

Re: Rolling Months

Thu, 09 Jun 2011 09:25:11 GMT

Hi guys, By using all the suggestions , I am able to achive what I wanted.

Thanks to all for Help!!!!!!!

My chart displays as the given attachment.But Now I want the sort order to be changed.Means it should first display Jan2010,Feb2010......Dec 2010 and the respectives values for each month should not change.

I am using the following code in my expression.

=(RangeSum(below(sum({$<DailyDashboardMonthYear=,Daily.Month=,Daily.Year=,DailyDashboardDate={"<=$(Variable1) >=$(Variable2)"}>}

NewDaily.Cum.EILR),0,12))

((

RangeSum(below(sum({$<DailyDashboardMonthYear=,Daily.Month=,Daily.Year=,DailyDashboardDate={"<=$(Variable1) >=$(Variable2)"}>}

(NewDaily.Cum.GWP)),0,12))

RangeSum(below(sum({<DailyDashboardMonthYear=,Daily.Month=,Daily.Year=,DailyDashboardDate={"<=$(Variable1) >=$(Variable2)"}>}NewDaily.Cum.1103),0,12))

)

RangeSum(below(sum({$<DailyDashboardMonthYear=,Daily.Month=,Daily.Year=,DailyDashboardDate={"<=$(Variable1) >=$(Variable2)"}>}

NewDaily.Cum.EILR1),0,12))

))

My dimension is :DailyDashboardMonthYear

Variable1:=Max(DailyDashboardDate)

Variable2:=Addmonths(DailyDashboardDate,-23)