Forum Discussion
Previous month value - wrong metadata
Hi All, initial dataset is as in the upper table - Client has a certain Sale in each month, from Jan till September - belongs to Hong-Kong office, later office gets changes to London. How to writ...
Hi,
here is a dataset
Date:
| Date | Calendar Month Number | Calendar Year |
| 2016-01-01 | 1 | 2016 |
| 2016-02-01 | 2 | 2016 |
| 2016-03-01 | 3 | 2016 |
| 2016-04-01 | 4 | 2016 |
| 2016-05-01 | 5 | 2016 |
| 2016-06-01 | 6 | 2016 |
| 2016-07-01 | 7 | 2016 |
| 2016-08-01 | 8 | 2016 |
| 2016-09-01 | 9 | 2016 |
| 2016-10-01 | 10 | 2016 |
| 2016-11-01 | 11 | 2016 |
| 2016-12-01 | 12 | 2016 |
Office:
| OfficeID | OfficeName |
| 1 | Hong-Kong |
| 2 | London |
| 3 | Warsaw |
| 4 | Barcelona |
| 5 | Delhi |
Client:
| ClientID | ClientName |
| 1 | Client A |
| 2 | Client B |
| 3 | Client C |
| 4 | Client D |
Fact:
| ClientID | DateID | OfficeID | Sales |
| 1 | 2016-01-01 | 1 | 10 |
| 1 | 2016-02-01 | 1 | 20 |
| 1 | 2016-03-01 | 1 | 34 |
| 1 | 2016-04-01 | 1 | 546 |
| 1 | 2016-05-01 | 1 | 43 |
| 1 | 2016-06-01 | 1 | 324 |
| 1 | 2016-07-01 | 1 | 65 |
| 1 | 2016-08-01 | 1 | 45 |
| 1 | 2016-09-01 | 2 | 7 |
| 1 | 2016-10-01 | 2 | 6 |
| 1 | 2016-11-01 | 2 | 3324 |
| 1 | 2016-12-01 | 2 | 54 |
Expected result (just one row for month 9, with both sales and prev_mont_sales in the same row, with the Office, which exists in Fact, which is London, not Hong Kong):
| Year | Calendar Month Number | ClientName | OfficeName | Sum of Sales | Sales_PrevMonth |
| 2016 | 1 | Client A | Hong-Kong | 10 | |
| 2016 | 2 | Client A | Hong-Kong | 20 | 10 |
| 2016 | 3 | Client A | Hong-Kong | 34 | 20 |
| 2016 | 4 | Client A | Hong-Kong | 546 | 34 |
| 2016 | 5 | Client A | Hong-Kong | 43 | 546 |
| 2016 | 6 | Client A | Hong-Kong | 324 | 43 |
| 2016 | 7 | Client A | Hong-Kong | 65 | 324 |
| 2016 | 8 | Client A | Hong-Kong | 45 | 65 |
| 2016 | 9 | Client A | London | 7 | 45 |
| 2016 | 10 | Client A | London | 6 | 7 |
| 2016 | 11 | Client A | London | 3324 | 6 |
| 2016 | 12 | Client A | London | 54 | 3324 |
Of course we have to assume, that there can be many more dimensions (it's just Office in this example), and measure should behave the same way against all of them.
We can assume that prevmont_sale can be aggregated by any dimension apart Date and Client, but associated with current month's dimensionality.