Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
To fully understand my problem please look at the following sample data
Calendar table:
DateKey Comparable Date
2008-01-21 2007-01-20
2008-01-22 2007-01-21
etc.
Sales table
Date Key Sales
2007-01-20 100
2007-01-21 200
....
2008-01-21 50
2008-01-22 70
What I want to be able to do is get a straight table when i select a year and month that looks like the following:
DateKey CurrentSales PreviousSales
2008-01-21 50 100
2008-01-22 70 200
Not sure what kind of formula or set analysis to use for this. Any suggestions would be much appreciated thanks.
John Witherspoon seems to have solved this eternal issue, give this a read:
New and simpler version of my solution just posted in the referenced thread. As far as your specific data, you would rearrange your tables like this:
DateKey Date Type Record ID
2008-01-21 Previous 1
2008-01-22 Previous 2
2008-01-21 Current 3
2008-01-22 Current 4
Record ID Sales
1 100
2 200
3 50
4 70
Then you just create a pivot table with DateKey and DateType as dimensions. Use sum(Sales) as the expression. Override the label for DateType to call it Sales. You should end up with this:
DateKey Sales Current Previous
2008-01-21 50 100
2008-01-22 70 200
Note that each record ID will be associated with TWO DateKeys, which isn't obvious from your specific data. Which ID you use (and thus which date you're looking at) depends on the Date Type. By the same token, each DateKey is associated with TWO days of data, so you may need to use set analysis to narrow down to one Date Type when you don't want to see both.