Rolling 12 months

[Anonymous]

Hi

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]

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 · Accepted solution]

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]

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