Nested transformations? 1

[bmcwest]

Here's the sitch'. User wants to pick a single quarter for a report. Based on the quarter selected the report needs to return the last month of each quarter for the trailing 12 quarters. Simple enough except that the calculations at each quarter end month must be for a trailing 12 months. I have come at this problem from so many angles but am still stumped. Tried nesting transformation to no avail. Tried to come up with a report that returned the last month of each quarter for the trailing 12 quarters what I could use as a filter for another report but I can't quite get that to work either. Next step is tech support but I though I would give this forum a shot first.

Thanks in advance for any ideas.

 

[nlim]

transformations here would be too hairy I think. Here's what I did with the tutorial project that seemed to work. It assumes that the ids of your quarters increase with each new quarter, and that your monthid increases within each quarter.

First create a report that shows the trailing 12 quarters. The only catch is that you need to use value prompt for quarter.
1) create metric M1 max(quarter)
2) create M2 runningcount(M1)<SortBy= (Value Desc) > You can set the parameters by highlighting 'runningcount' and RMC.
3) create R1 with quarter and M2 on template; for filter add M2<=12 and quarter@id <= valueprompt

When you run R1 you should be prompted for a quarterid, and then get a list of quarters. M2 is just the trailing quarter number and you are setting it to be last 12 by the filter.

Then you need to find the month number in each quarter. Here's how
4) create M3 max(month)
5) create M4 runningcount(M3) <BreakBy={Quarter}, SortBy= (Value) > This means that M4 is the month number in each quarter. So M4 value of Jan2003, Apr2003 are both 1. Obviously M4 value of Mar2003, June2003, Sep2003 are all 3 (the 3rd and last month in each quarter)
6) create R2 with month on template; filter should be R1 (the quarters you are interested in) and M4=3 (the 3rd month in each quarter).

It works on my tutorial project, let us know if you get it to work. good luck.

 

[bmcwest]

I'm tracking on this very helpful post with the exception of step 2 while creating M2. I don't understand what (Value Desc) under sortby means. I have created the metric without the sortby but wind up getting quarters from 1999 even when I enter quarter ids from 2000 onward. I really appreciate your insightful response to this question and I am amazed at your depth of understanding. Thanks.

 

[nlim]

in step 2, when you create the metric, you should highlight the runningcount function and RMC. You should then see a dialog with 3 tabs. pick the Sort by tab and select sort by expression descending. If you want to check if you have done it right, in one of the menu bars find the &quot;show function parameters&quot;. The metric should look exactly like I wrote. If you don't use the sortby then you will not be able to limit the quarters you want to the trailing 12 quarters, just to quarters before your prompt selection.

M2 is the metric that counts the trailing quarters backwards. if you pick 2000Q1 then M2 for 2000Q1 is 1, M2 value for 1999Q4 is 2, M2 value for 1999Q3 is 3 and so forth. You want the report to cut of at M2=12, ie last 12 trailing quarters.

I think your quarters so far are right because you want the 12 trailing quarters. So if you pick 2000Q1, you should get 2000Q1, 1999Q4, 1999Q3 etc. Now you just need to add the filter to get the last 12 (or however many quarters you want). These quarters become the filter for the next report, which will get you the last month in each of these quarters.

feel free to post again if you need clarification.

 

[nlim]

bmcwest, I forgot the second part of your question. It looks like after you have gotten the 12 ending months of the trailing 12 quarters, you still need to create a metric for the trailing 12 months... so i assume your final report R3 looks like

MonthA sum(x)for last 12 months.
monthB sum(x)for last 12 months.

Here's where I would suggest you use transformations. fairly straightforward transformation table, with 1-to-many relationship. add this transformation to your metric.

Just use R2 as the filter for your final report.

 

[bmcwest]

Your solution worked perfectly. It is interesting how you approached the problem without using TO's. I would have never taken the angle you did to arrive at the solution. My hat is off to you!

If I may ask, how did you gain such a good knowlege of the functions? I can never seem to get a descent answer from the manuals.

 

[nlim]

don't deserve the accolades. trial and error, and mostly just clicking on all available buttons to see what happens.

Things got really better after they introduced report as filter. Using relationship filters in the pre 7i versions was quite difficult...

good luck with the rest of your implementation.