Here is my requirement,
Fact table contains Week, ID and Status
If there is no data for a week, chart needs to show the previous week's data.
For example,
Week | ID | Status |
---|
2012w01 | 1 | Y |
2012w01 | 2 | N |
2012w01 | 3 | Y |
2012w02 | 1 | Y |
2012w02 | 2 | Y |
2012w04 | 1 | N |
Now the below chart needs to be built,
Week | Completed (count of ID with Status 'Y') | NotCompleted (count of ID with Status 'Y') |
---|
2012w01 | 2 | 1 |
2012w02 | 3 (ID-1,2 from the above table, ID-3 is not available in the above table, so it has to consider the status of ID-3 in Week 2012w01) | |
2012w03 | 3 (ID 1,2 from 2012w02, ID-3 from 2012w01) | |
2012w04 | 2(ID 1,2 from previous weeks) | 1 |
Is it possible to create this chart in UI itself?
Since we dont have ID in the dimension, above function will not work.
Any idea?