Set analysis -variable usage-for date selection.

[#1]

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

[jonathandienst]

You are trying to do a row by row calculation. This cannot easily be done using set expressions, as they are evaluated once for the chart and not once per row.

I suggest you read this article which will explain how you can do what you require: Canonical Date

[PradeepReddy]

see the attachment.. it might be helpfull