Hi Experts,
I need to show a pivot table chart which should appear as below.
| Channel | Process Name | Sales |
|---|
| Email | Process ABC | 15 M |
| Facebook | Process XYZ | 25 M |
| Postal Mail | Process QWE | 5 M |
I have source file in the form of two tables.
Table 1 appears as follows:
| Order Key | Order Date | Sales |
|---|
| 1 | 1/2/2015 | 12 M |
| 2 | 2/4/2015 | 2 M |
| 3 | 2/6/2015 | 0.5 M |
Table 2 appears as follows:
| Process Key | Process Name | Process Start Date | Process End Date |
|---|
| 1 P | Process ABC | 1/1/2015 | 28/2/2015 |
| 2 P | Process XYZ | 1/4/2015 | 24/4/2015 |
| 3 P | Process QWE | 1/6/2015 | 27/6/2015 |
In my requirement, there is a necessity to keep both the calendars independent.
Now, I need to select the order dates of Table1 which falls under the corresponding process date range in the Table 2. Finally I need to show the respective total sales.
In expression we have something like below.
=Sum( {<$(Variable1),$(Variable2),OrderKey=p({<$(=vProcesssDateCheck)>})>} Sales)
where vProcesssDateCheck='[Order Date]={">=$(=([Process Start Date]))<=$(=([Process End Date]))"}'
It works fine when a particular Process Name is selected. But as a whole the Process date Range is not filtered by the variable for each record, showing '0' for Sales.
Any help would be much appreciated.
Thanks,
Barathiraja
