Rolling 12 Month Total

[#1]

Hello,

        I'm trying to create a 12 month rolling total, however I cannot get set analysis to cover  the proper period range.  I have attached an example document with problem and all data.  I created a text box within the document with additional information.

Thanks,

TheHiker

[johnw]

I guess I'm not clear what you're after, either with the previous question or this one.

If you want to use the week from the master calendar, for instance, you'd just use the week from the master calendar.  Did you want a rolling 12 months ending with that week or something?  Maybe you need an AsOfDate instead of an AsOfMonth?

I admit that an AsOf table can confuse selections, though I'm unclear if you're having the sorts of problems I might expect.  One possibility is selecting and locking the 'current' period type.  That way, charts by default look only at the current period even when using the AsOfMonth.  And then ONLY when you want the AsOf functionality, override the period type with set analysis.  At that point, you'd probably just name all your AsOf fields to regular fields like "Month", and have to rename the fields in the real calendar, because you'd be hiding them from the user.

As for the YYYY-MMM question, what would you mean by splitting apart year and month separately?  If you have a MMM field, a month(Date) field in the calendar table, I'd think you could use it just fine.  Maybe I'm not thinking of obvious problems, though.  But if the month field is in or related to your AsOf table, then you need to establish some definitions.  What does it mean, after all, to have a rolling 12 months ending with April, when the year isn't specified?  It seems meaningless.  Well, I suppose not.  You could have the as of year as a separate dimension.  So you're comparing the rolling 12 month period ending in April across years.  I suppose that makes sense.  I have no idea if you're after something like that, though.

[Anonymous]

Hi John,

Thanks a lot again for your reply. I think I didnt ask the question clearly.

Basically I have 2 tables 1 called called Japan and the other called US which I am concatenating. After concatenation, I get 1 table called Final_Table.

Japan has the maximum date of June 2011 and US has the maximum date of Apr 2011.

In this Final_Table I have a MonthYear value which I am using to create AsOf Table using the code below.

PeriodTable:

Load date(FieldValue('MonthYear',RecNo()),'MMM-YYYY') as Period

AutoGenerate FieldValueCount('MonthYear')

;

AsOfPeriodTable: // Creating Current and Rolling 12 period type

Load

Period as AsOfPeriod,

'Current' as PeriodType,

Period as MonthYear

Resident PeriodTable;

Concatenate (AsOfPeriodTable)

LOAD

Period as AsOfPeriod,

'Rolling 12' as PeriodType,

date(AddMonths(Period,1-IterNo()),'MMM-YYYY') as MonthYear

Resident PeriodTable

While IterNo() <= 12;

Inner Join(AsOfPeriodTable)

LOAD Period as MonthYear

Resident PeriodTable;

DROP Table PeriodTable;

Once its all done, I am trying to use AsOfPeriod as my main selection.

Now, when I click on Japan, the date is correctly shown in both MonthYear and AsOfPeriod but If I select US, then AsOfPeriod is shown the same as Japan. I think AsOfPeriod is picking up the maximum date for both. for Example, if I had July 2011 as the maximum date for Japan then US would also become July 2011. Thisi s shown in the picture I have attached below.

AsOf Period should be exactly equal to MonthYear, isn't it? How can I fix this?

As usual I would really appreciate your reply!

Thanks

[Anonymous]

Hi John,

To make it easier to understand, I have attached the same application you built.

The issue I have, is with the AsOf Period showing wrong dates.

in the application attached you had 1 table called ExpenseTbl which had 201106 as the max date. I have concatenated another test table with ExpenseTbl which contains the max date as 201110. My test table contains period and ISGroup1.

Once the concatenated table is loaded, and I click on a value in ISGroup1 thats from ExpenseTbl table, the AsOf Period shows the maximum possible selection as 201110. That means the background of 201110 is white not grey. Which should be grey, as there was no date as 201110 in the ExpenseTbl. 201110 was only in the test table which I concatenated with the ExpenseTbl. Hence, its giving a wrong impression to user.

I'm not sure, whats causing this problem, is there any way to fix it?

Its a bit important to what I am doing also I am learning AsOf technique thats why I am asking this.

Hope its I'm not causing any inconvinience for you.

Many thanks for your help in advance.

[johnw]

Sorry for my slow response.  I've been very busy, but hopefully I can take a look today or at least some time this week.

[Anonymous]

No problem John. Thanks for your reply. I'll wait for your response.

[johnw]

I swear I still plan to get around to this.  It looks like an interesting question.  Later today actually looks good unless something new comes up.  There's been a LOT new coming up recently.  I guess that's actually good instead of bad since I like my job.  *chuckle*

[johnw]

OK, I see what you're talking about in the file.  In a sense it's right, because 201106 IS in the 12 month rolling average ending in month 201110.  However, I can understand that you don't want to see anything past the maximum period that has been reported for a given group.  I wouldn't want to either.

I gather that each ISGroup1 has a maximum period that has been reported, and that these periods can differ.  You only want to report data up to these periods.  If you select Total Directs, it shouldn't "magically" give you data for 201110, but only give you data up through 201106.

ISGroup1             max(ISPeriod1)
Expenditure          201110
Sale                 201108
Total Directs        201106
Total Indirects      201106
Total Other Charges  201106
Total Revenue        201106

One way to enforce those maximums would be to add the ISGroup1 to the AsOf table.  Generate a separate set of data for every ISGroup1.  For that matter, it seems like maybe if a month is missing from your data, in your case, you don't want to generate an AsOf for that month, as that would seem to imply that that month DOES have data.  Ah, you were already taking that into account by only starting with existing periods.  We just need to constrain it further to only periods that exist for that specific ISGroup1.

It'll load a little more slowly, but I think then you want to start with distinct combinations of ISGroup1 and ISPeriod1 in your main table.  You can otherwise use almost exactly the same logic you had before.  I've made the minor changes in the attached file.  It at least seems to solve the problem for Total Directs.

I'm not sure I've answered all your questions, though.  If you still have questions about year and month separately, or reporting for weeks, and other things you've mentioned but I'm not sure I've understood, please ask them again, and ideally referring to the example you posted, or modifying it to show specific problems or things you want to accomplish.