How to model TY v LY without DMR?

[patrickthatcher]

I must have been overwhelmed in my FM class and forgot to ask this question about modeling for TY v LY type report.  We are using a columnar database and hoped that we wouldn't need to create a cube to complete this task (not opposed to doing this, but wanted to test if this was possible.)
My Transaction table is structured as such
Tran_Date   Tran_Type   Product_ID   Qty   Dollar
4/15/2015   Ship                   12452          10   155
4/15/2015   Ship                   12487            3   33
4/16/2015   Ship                   44578            4   3
4/17/2015   Return           12452            1   15.5
4/16/2014   Ship                   12452            7   78
4/16/2014   Return           12487            1   3
4/17/2014   Ship                   44578          25   75

My Date table will have the corresponding Date for last year. For example:

TY_Date   LY_Date
4/15/2015   4/16/2014
4/16/2015   4/17/2014
4/17/2015   4/18/2014

I thought maybe the samples might have had an example but there wasn't one

Thank ou

[Lynn]

I don't have experience with columnar databases but I suppose that would still translate to a relational approach.

If so then the method for this is rather tedious and can be done on the report side. An explanation is below.

I've never explored the idea of doing it in a purely relational model so maybe someone else might pitch in with ideas. You can produce a DMR model rather than a cube, but either approach would be the typical way to go.

The approach on the reporting side would be to filter the query for both ranges of desired dates. This example is hard coded values but you would replace these with parameters most probably:

Code Select Expand


[tran_date] between 2015-04-15 and 2015-04-17
or
[tran_date] between 2014-04-16 and 2014-04-18


Next you'd include a query item for this year quantity and another for last year quantity. In each you would interrogate the date to pick up the quantity for a particular value or else use zero.

Code Select Expand


#/* This year quantity */#
case when [tran_date] between 2015-04-15 and 2015-04-17 then [Qty] else 0 end


Code Select Expand


#/* Last year quantity */#
case when [tran_date] between 2014-04-16 and 2014-04-18 then [Qty] else 0 end


Use the same approach for dollars. Now your query has 4 measures with the quantity and amount values "bucketed" into separate query items for use in the report layout. A crosstab layout is another method to consider. Filter as I described above and then put a year element in the crosstab, which would bring back 2014 and 2015, and then nest your quantity and/or dollars beneath to split out the figures.

Hope this gives you some ideas to consider.

[patrickthatcher]

Thanks!  I'll give this a try