topic Re: Rolling 12 months in QlikView

Rolling 12 months

Anonymous — Wed, 31 Aug 2011 10:37:04 GMT

I am trying to get a chart to stay locked to last 12 months even if user trys to change dates.

The table itsefl only consists of reporing date and sample number.

So in short I need to graphs one that allways shows the last rolling 12 months of count, and one the shows the last 12 months based on user selection

In my load I have:

LOAD

Year(AddMonths("Reporting Date",0)) & Num(Month(AddMonths("Reporting Date",0)),00) as Period, // gets perod like YYYYMM from reporting date

Year(AddMonths(today(),-13)) & Num(Month(AddMonths(today(),-13)),00) as Startdate, // sets a startdate to 13 months back from today YYYYMM

Year(AddMonths(today(),0)) & Num(Month(AddMonths(today(),0)),00) as Enddate, // sets enddate to todays month YYYYMM

Num(Month(AddMonths(Bed.Dato,0)),00) as Month2,

(Year(AddMonths(Bed.Dato,0)),00) as Year2,

"Reporting Date", // datetime from table

SampleNo;

My expression looks for the rolling last 12 months

count({$<[Reporting date]=,Period={">$(Min(Startdate))<$(=Min(Enddate))"}>} SampleNo)

How ot solve this?

its_anandrjs — Wed, 31 Aug 2011 10:45:09 GMT

Hi,

I suggest you do some changes in your Count statement like

Count({<YearMonth ={">=$(=Date(addmonths(Max(YearMonth), -12), 'MMM-YY')) <=$(=Date(addmonths(Max(YearMonth), -1), 'MMM-YY'))"} >} SampleNo)

by this you get rolling 12 months

Hope this helps you.

See the attched sample

Regards

Anand

swuehl — Wed, 31 Aug 2011 10:48:09 GMT

I think there is an equal sign missing before Min(StartDate), then I would also clear Month2 and Year2, so try this

=count({$<[Reporting date]=,Year2=, Month2=,Period={">$(=Min(Startdate))<$(=Min(Enddate))"}>} SampleNo)

Since Startdate and enddate are based on today() only, you could use variables instead of fields for that.

I haven't checked thorougly your expressions in the script, I assume the format of Period and StartDate and Enddate is consistent.

Regards,

Stefan

Anonymous — Wed, 31 Aug 2011 10:54:49 GMT

Hi swuehl, Your code worked like a charm. Thanks

Anand: I tired your code but I got some error. will try it out when I get this app up running Thanks for your insight.

Dan