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 |
Ashish_Mathur
Super User
Super UserHi Ashish,
thank you, this looks great. But how to make part with ",all(Office)" more generic? So let's say basically we should remove any filter on every possible table APART from Date and Client dimensions.
sth like this should work - REMOVEFILTERS(), KEEPFILTERS(Dates), KEEPFILTERS (Clients) ?- Ashish_Mathur
Hi,
You are welcome. Try the ALLEXCEPT() function.
