2 Replies Latest reply on Aug 13, 2013 3:01 PM by Michel Caissie

# Date relationship problem

I have the following example.

- 2 Connections from the SuperStore called  Primary and Secondary

- Keep only relationship on [Order Date]

- On secondary , create a calculated field   SUM[Sales]

Now from the Secondary connection  i create two sheets, i filter two single dates ( i want to compare two days)

On the first sheet i bring the full date  in the view (Year,Quarter,Month,Day),  on the second sheet i bring only the day.

In both sheet i bring the SUM[Sales]  and as expected i get the same values in both sheets.

Now i do the same thing from  the Primary connection except that i bring the SUM[Sales]  from the secondary.

On the sheet with the full date in the view , i get the same numbers as in the previous sheet ( as expected)

But if i only bring the DAY[OrderDate] in the view i get totaly different numbers.

Is this a bug or did i missed something ?

thanks

Michel

• ###### 1. Re: Date relationship problem

It's working as expected. When you add the Secondary SUM(Sales), it's blending data based on the fields you have on your view. In this case, DAY(Order Date) (Primary).

Instead of filtering the Secondary datasource on the full date, it's aggregating by day and only keeping Day(Order Date) = 1 or 2.

The large numbers you are getting are essentially the sum of Sales that happen on the 1st or 2nd of any month, not the 2 days your are interested in.

You can see that by creating a new view with DAY(Order Date) and filtering where Day = 1 or 2.

If you want to see the results you were expecting, you have to add a specific date filter on the Secondary Order Date (Date = 1/1/2009 or 1/2/2009).

Does it make sense?

Pedro

• ###### 2. Re: Date relationship problem

Hi Pedro,

It make sense.

I can see in the worksheet from Secondary  that if i removed the filter,  the number corresponds to the SUM of all 1s  and all 2s.

So what i missed  is that  the Blending is based on the fields in the view  and not only on the fields in the Filters.

I am not sure i feel it coherent, it seems obvious that you want to blend on the exact same date that you filter.

I would have expected that it query  Dec 1 from Secondary  and  then apply DAY[Order Date] in the view.

Anyway , thanks for clearing this up

Michel