×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Displaying single date when metrics filtered by two diff date attr

Displaying single date when metrics filtered by two diff date attr

Displaying single date when metrics filtered by two diff date attr

(OP)
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
 

RE: Displaying single date when metrics filtered by two diff date attr

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)
 

RE: Displaying single date when metrics filtered by two diff date attr

(OP)
thanks reisw, I will give that a try.

Toni

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close