Hi QV Members,
I was stuck at a point where we need to calculate the share change ie Current quarter - previous quarter. We have 2 different tables:
1) First table contains only the latest quarter data for each specific Plan (ex: one plan contains data for Q3'15 other plan may contain data for Q2'15) the quarter for each plan may be different. We are showing Plan - Quater and market share.
2) Second table contains 4 quarters of data for each Plan. (As shown in the below excel)
Now we need to calculate share change, so our requirement is we need to show plan -quarter and market share from first table and while calculating share change we need to find Latest Quarter for each plan from the first table and need to calculate Previous quarter (Max quarter from first table-1) from the second table and need to do Current qtr sales- Previous qtr sales.
There may be a multiple approaches for this but it would be great if any one help me at data model level by doing some mapping or any other methods.
Thanks in Advance
Table 1:
| Plan | Quarter | Sales |
| Plan 1 | Q3'14 | 34 |
| Plan 2 | Q2'14 | 23 |
| Plan 3 | Q1'14 | 213 |
Table 2:
| Plan 1 | Q3'14 | 34 |
| Plan 1 | Q2'14 | 24 |
| Plan 1 | Q1'14 | 54 |
| Plan 1 | Q4'13 | 143 |
| Plan 2 | Q2'14 | 43 |
| Plan 2 | Q1'14 | 54 |
| Plan 2 | Q4'13 | 45 |
| Plan 2 | Q3'13 | 24 |
| Plan 3 | Q1'14 | 45 |
| Plan 3 | Q4'13 | 43 |
| Plan 3 | Q3'13 | 67 |
| Plan 3 | Q2'13 | 567 |
