Displaying single date when metrics filtered by two diff date attr

[mstrpup]

Hi folks. I hope someone can help me with this.

I’m trying to display two metrics filtered by two different date attributes on the same report with one single date displayed.

I need to display dollars ordered and dollars invoiced by day. Dollars ordered is based off of “Order Date” and dollars invoiced is based on “Invoice Date”. I have a metric for each and each contains a filter using the appropriate date. They work fine if I do not display a date on the grid. However, I need a date, as shown below…

Date Ordered Invoiced
1/1/09 5,000 2,500
1/2/09 2,000 3,000
1/3/09 6,000 4,000

I need the date on the report to reflect both ordered date and invoiced date and the appropriate amounts to fall in the correct column

If I user either Invoice Date or Order Date as the display, the data doesn’t display correctly by date (seems like I am getting two rows for each date and some data displays in the wrong date)

I tried creating a third date, with Invoice and Order dates as it’s children. When I use this date in the grid I only get data where the two dates are the same on the sales order.

Here is my setup…

Tables
LU_CALENDAR_DATE
Cal_Date_ID (INT)
Cal_Date (Datetime)

Alias Views over LU_CALENDAR_DATE
AV_CAL_DATE_INVOICE
AV_CAL_DATE_ORDER

FACT_SALES_LINE
Order_No
Order_Dollars
Order_Date_ID
Invoice_Date_ID


Attributes

Invoice Date
ID = Cal_Date_ID (from AV_CAL_DATE_INVOICE);
Cal_Date_ID (from LU_CALENDAR_DATE) – this was added when trying 3rd date;
Invoice_Date_ID (from FACT_SALES_LINE)

Order Date
ID = Cal_Date_ID (from AV_CAL_DATE_ORDER);
Cal_Date_ID (from LU_CALENDAR_DATE) ) – this was added when trying 3rd date;
Order_Date_ID (from FACT_SALES_LINE)

Date – Test
ID = Cal_Date_ID (from LU_CALENDAR_DATE)
Children = Invoice Date and Order Date

Metrics
Invoiced Sales – Order Dollars, where Invoice Date within date range
Ordered – Order Dollars where Order Date within date range


Thanks in advance for any help you can offer!

~Toni

 

[reisw]

Toni,

What you need to do is create an alias of your fact table and then have one date attribute that maps to order date on one side and then invoice date on the aliased fact table. You should get the results you are looking for. You can create an alias of an existing table by right clicking on the table and selecting "Create table alias"

FACT_SALES_LINE
Order_No
Order_Dollars
Order_Date_ID
Invoice_Date_ID

FACT_SALES_LINE (Order Date)
Order_No
Order_Dollars
Order_Date_ID
Invoice_Date_ID

Map "Date" to Invoice_date_id on FACT_SALES_LINE
and
Map "Date" to Order_Date_id on FACT_SALES_LINE (Order Date)

 

[mstrpup]

thanks reisw, I will give that a try.

Toni