Please help with set anlysis syntax for distinct dates

[#1]

Hi all,

Hoping someone can help me please. I'm trying to create a chart merging two data sets.

The first data set is orders which takes the form-

ORDERID, CUSTOMERID, ORDERDATE, ORDER_DATE_MONTH etc

123,          12345,          13/4/2014,           1/4/2014

The second data set is the forecast and looks like -

CUSTOMERID,  FORECASTDATE,  FORECAST

12345,      1/4/2014,     100

I have created the first part of the chart okay using the first file. I have converted ORDERDATE to a month string as the dimention and counted the order numbers.

In order to include a line for the forecast on this chart I have added the second file. The join key is customer ID as I want to be able to filter on customer and see only their sales and forecast as well as the total and some other groupings.

The forecast is monthly and the date format is 1/6/2014 where the date is always the first of that month. I have created a date to compare to using the order date and monthstart, which I have called ORDER_MONTH_DATE

I'm thinking I need to sum the forecast where the FORECASTDATE = ORDERDATE but I am getting incorrect figures as it's summing EVERY TIME it finds that date in the order file.

My query sort of works and looks like this -

sum (if(date#(FORECAST_DATE, 'DD/MM/YYYY' = ORDER_MONTH_DATE), FORECAST))

but what i need it to do is something like this -

sum(if(date#(FORECAST_DATE, 'DD/MM/YYYY' = distinct(ORDER_MONTH_DATE)), FORECAST))

So that it only finds one instance of each ORDER_MONTH_DATE,  but the above query doesn't work.

I'd really appreciate it if someone can solve this for me please!

Thanks in advance

[#2]

Sorry... I lied!.... the query I'm using is -

  sum (if(date#(FORECAST_DATE, 'DD/MM/YYYY') = ORDER_MONTH_DATE, FORECAST))

Thanks!