based on other date

[Anonymous]

Hi all,

I have 5 tables and are linked with each other on a common field .. All these 5 tables have date field each which are linked to master calender. Out of 5 tables , one of the table is show below

Table1:

load

id,

orderdate as date

delivery date ,

sales

from qvd;

Master calender has date, year,month day based on the table1.

Now , i need the sum of sales and distinct id where the delivery dates is with in the 60 days from the  date selected  .

For example:

if i select 04/14/2016 , then i need sum of sale & distinct id where delivery date is in between 04/14/2016 to 06/14/2016 .

if i select month as Apr, then i need sum of sale & distinct id where delivery date is in between 04/01/2016 to 05/31/2016 .

Kindly help me ...

Thanks in advance .

[#2]

By using set analysis we can achieve

[avinashelite]

try like this

Sum({<Date = {">=$(=Date(Max(Date)-60)<=(=Date(Max(Date)))"}  >} Sales)

count( DISTINCT {<Date = {">=$(=Date(Max(Date)-60)<=(=Date(Max(Date)))"}  >} ID)

[Anonymous]

hi..

thanks for reply ...

i tried but failed .. but u help me in providing the set analysis expression ..

[Anonymous]

hi,

i need sales based on the delivery date between the range of date i selected .

[avinashelite]

above expression is for the 60 days date range only ...are you selecting both the START and END date ??

if that is the case capture the selection in variable and try like this

Sum({<Date = {">=(='$(vStartdate)')<=(=(='$(vEnddate)))"}  >} Sales)

count( DISTINCT {<Date = {">=(='$(vStartdate)')<=(=(='$(vEnddate)))"}    >} ID)