Working with separate date fields in one chart

[D1234]

Hello,

I am currently working on a report in which I have two different date fields each with their own information set. I have a separate WeekEnd field which gives me the week ending date of the information based on the most recent sunday. Both of these Date fields need to be displayed on a chart together. The issue is that I can only either get accurate information for one or the other but not both. I think it may be due to the table structure in part.

The structure is as follows:
WeekEnd     [Date1]     [Date2]     [X amount charged]


I am able to create separate graphs which are accurate. However I am struggling to find a way to combine them onto one graph. Is there a way to combine two separate charts into one? Additionally, these two are in the same query. Is there alternatively a way to combine charts from two different queries into one chart?

Thanks

[sdf]

in a relational data source both date data items should have a relationship with each other.
can you create a simple list and drag both dates inside plus a measure and a dimension just to check. or check the data by running tabular data.

[D1234]

The issue is that [Date1] and [Date2] are related in such a way that makes graphing them together difficult. Specifically, there is a relationship between the two that [Date1] must occur before [Date2]. For one record, there is both a recording on [Date1] and then later on [Date2]. It can become messy when both date fields are put into the same list as a result. This is the reason why creating separate graphs is no issue. Mainly I am curious if there is a way to put these two separate graphs together.

Thanks

[Lynn]

The issue is that [Date1] and [Date2] are related in such a way that makes graphing them together difficult. Specifically, there is a relationship between the two that [Date1] must occur before [Date2]. For one record, there is both a recording on [Date1] and then later on [Date2]. It can become messy when both date fields are put into the same list as a result. This is the reason why creating separate graphs is no issue. Mainly I am curious if there is a way to put these two separate graphs together.

Thanks

It sounds like your framework manager model does not entirely support necessary reporting requirements. It is often the case that a fact has more than one date associated with it (e.g., date ordered and date delivered). These would be modeled as separate dimensions allowing for accurate reporting results using one date or multiple dates. If you have any opportunity to address this problem in your data model it will save a lot of time and effort in reporting.

You don't mention what type of graph you are trying to produce but you will find that most have the ability to reference a single query so creating a chart that refers to two queries seems unlikely. You don't describe the elements you are trying to graph, but perhaps you can explore creating two separate queries and then use a query join or union to put the results into a single query for purposes of your graph.

[D1234]

I think you are correct in saying a join is the best way to achieve this. I am trying to create a line graph which shows two different fields, one associated with each date field as its own line and how they change over time. Would a join be able to accomplish this?

Thanks

[Lynn]

I think you are correct in saying a join is the best way to achieve this. I am trying to create a line graph which shows two different fields, one associated with each date field as its own line and how they change over time. Would a join be able to accomplish this?

Thanks

You posted a new thread with a similar sounding question here: http://www.cognoise.com/index.php/topic,34079.0.html I'm not sure if the information in that post pertains to this same problem or not.

The attached report spec over the sample data has a query showing revenue by close data and another query showing revenue by ship date. Each has a query calculation that is simply a string to identify which date type the query is providing.

These two queries are put together using a union so that the resulting query can be used to plot revenue by month with one line representing revenue by close date and another line representing revenue by ship date. The results of both individual queries are shown in list form while the combined result is shown in a crosstab as well as a line graph so you can see the figures are correct in all situations.

Hopefully this helps solve your problem.

[D1234]

Thanks for providing this sample report.

I will take a look. Hopefully this can solve my issue.