Rolling month for last 13 MONTHS (having 2 yrs data)

[PRIT AMRIT]

Guru's,

Pls see attached file, require a rolling report, meaning that the report should always show 14 columns staring with current month down to month-13. This is why you can see in each column a single filter counting from a starting month (e.g. Sep-2012) and summing up the full 24 months (e.g. down to Sep-2010). The next column then starts with starting month = current month-1 (e.g. Aug-2012) and counting all records back 24 months (e.g. Aug-2010).

Question is, can we do it wihout using UNION? Becasue the data size is huge and creating 13 odd querries (having 2 yrs filter each) and UNION, really would have a massive performance issue.

NOTE: I have a Relational Package.

Pls let me have some ideas? It's urgent. Thanks

Regards,
Prit

[Lynn]

Since your month buckets are fixed you could have a single query that brings in the entire date range of data needed and then use query items to bucket the metrics accordingly with case statements.

This would eliminate any local processing that might come into the picture using separate union queries.

[PRIT AMRIT]

If I understood, Firist I have to filter the query for the set of data, i.e in my case as per the attached screenshot I would be filtering the query for 3 yrs(Between CurrentYear_CurrentMonth and CurrenYear_CurrentMonth-36).

Next step: Woudl have to derive 13 query items (for rolling -13 months) using If/else or Case statements? Each case statement should have the logic to extract 2yrs data. If yes, how the case statment should look like?

Let me also tell you that the requirement is, each of the query items would have 2 yrs data, but it shouldn't Aggregate the Measure for all the months. Instead it shoudl show the Latest Month and latest day record.
E.g. If User select 2012-OCT, the firslt column in the report should give me data Between 2012-OCT and 2010-OCT. In OCT i have 3 records 01-OCT-2012, 02-OCT-2012,03-OCT-2012. The column should return 03-OCT-2012.

If the OCT doesn't have data, then it should return SEPT data, not aggregate the entire date range. If SEPT doesn't have any data it should give AUG. It should check till 2 yrs and see which month got the data and assign that value to the empty MONTH. Hope it is clear?

thanks for your help in advance.

[Lynn]

I'm not sure I understand

...
Let me also tell you that the requirement is, each of the query items would have 2 yrs data, but it shouldn't Aggregate the Measure for all the months. Instead it shoudl show the Latest Month and latest day record.
E.g. If User select 2012-OCT, the firslt column in the report should give me data Between 2012-OCT and 2010-OCT. In OCT i have 3 records 01-OCT-2012, 02-OCT-2012,03-OCT-2012. The column should return 03-OCT-2012.

If the OCT doesn't have data, then it should return SEPT data, not aggregate the entire date range. If SEPT doesn't have any data it should give AUG. It should check till 2 yrs and see which month got the data and assign that value to the empty MONTH. Hope it is clear?

thanks for your help in advance.

What I was suggesting is that your query filter for the entire date range as you indicated. Individual query items with case statements slot each record into the appropriate bucket. If the user selected 2012-OCT then you would derive the beginning of that range as 01-OCT-2012 and the end as 30-OCT-2010. Your case statement for that first bucket might be something like this:

Code Select Expand


case
  when [DateInYourData] between [BeginDateRange1] and [EndDateRange1] then [YourMeasureItem]
  else 0
end


This would be similar for each of the other buckets where just the date range criteria would differ in each expression. This would let you count or sum as needed. The requirement you added in your second post (don't aggregate all data, just show latest for the month) sounds conflicting with the first post (sum/count all 24 months).

The added bit about a month not having any data and then going back a month just complicates it further in my poor addled brain :)

[PRIT AMRIT]

Hi Lynn,

Thanks for your time. Well, the rquirement is bit conflict indeed. Let me try to explain you again with my POOR english  :P

This would be similar for each of the other buckets where just the date range criteria would differ in each expression. This would let you count or sum as needed. The requirement you added in your second post (don't aggregate all data, just show latest for the month) sounds conflicting with the first post (sum/count all 24 months).

Actually each of the data column bucket should have 2 Yrs data. so the case statement should look like

Code Select Expand

case
  when [DateInYourData] between [Oct-2012] and [0ct-2010] then [YourMeasureItem]
  else 0
end


Similarly the above should repeat for all the Month Bucket i.e. ( CurrentMonth till CurrentMonth-13).

Now, as we know, when we have a date range, the [MeasureItem] would be Aggregated for all the Months withing the date range. But my requirement is bit different. Instead of aggregating the for all months it should show only for OCT-2012. If OCT doesn't have data it should show SEPT-2012 data in OCT month bucket. If SEPT-2012 doesn't have DATA it should check for JULY-2012 data and fill in for SEPT & OCT bucket. If JULY-2012 doesn't have data, it should check the nearest month within the 2 yrs date range and fill in the same for OCT, SEPT, AUG, JULY month bucket.

Now the next step is, when it finds a MONTH having data. That Months might have several records. E.g. OCT-01-2012, OCT-02-2012,0CT-03-2012). In this case, it should pick the Latest Date value and show for that month.

Hope I have explained it better than last time?  ;) If yes, can I have your hidden ideas to achieve this report? Thanks Lynn.

Regards,
Prit
               

[PRIT AMRIT]

Hi guys.... I found a workaround for this and it works now. Thanks