Another Chart Question, Charting Tons Per Day

[CognosInMaine]

Bringing data in via a query, production day, which I'm doing a running count of the number of days, and tons of product produced on that day. I'm doing a running total of this number too. From these numbers I'm calculating out a tons per day. Below is an example of what I would expect to see:

Prod Day	Tons	Tons Per Day
4/1/2011	450	450
4/2/2011	456	453
4/3/2011	435	447

When I look at what is charted, the right most data point has a date of 4/3/2011, but the Tons per day number looks like it is running in reverse, meaning it is starting to calculate with the 4/3/2011 date and working back to 4/1/2011, making the data look like this:

Prod Day	Tons Per Day
4/1/2011	447
4/2/2011	445.5
4/3/2011	435

Not sure I explained the problem so others can understand it... Makes sense to me though. :)

Suggestions?

Thanks

[Lynn]

Are you using running-average in the query to compute the [Tons Per Day]?

Did you try setting a pre-sort on the [Prod Day] query item in the query so that the running computation follows the order of data retrieval and display?

Is it tons on lobster?? Yum. I think a little too early yet for that volume, though :D

[CognosInMaine]

I've tried pre-sorting on prod day, made no difference. Not lobster, wish it was i could retire then. :)

What is funny if I take the data items and toss them into a list box below the graph I get the results I'm expecting.

Here is how I'm getting the Tons per day:
IF ( [Cum Oper Days for Report] <> 0 ) THEN
    ( [Cum Actual Net Tons for Report] / [Cum Oper Days for Report] )
ELSE
    ( 0.0 )

Cum Oper Days for Report is found by doing this:
[Total Operating Days for Report] - (running-total ([Actual Operating Days]) - [Actual Operating Days])

And for Cum Actual Net Tons for Report
[Total Actual Net Tons for Report] - running-total ([Actual Net Tons]) + [Actual Net Tons]

Thanks for your help Lynn

Actual Operating Days is dir4ctly from the frame work
[Production (query)].[Daily Production Fact].[Actual Operating Days]

This is because an operating day can be less then one at times (exempt down time)

[Lynn]

Hmmm.....this looks like one of those things I hack around with until I find a way to make it work. There are others on the forum who understand the inner workings of these things and can generally offer better advice. Hopefully they'll turn up with more thoughts.

I don't know why the list would show you that your calculations are correct while your chart would behave differently. Are there additional elements shown in the list that are not in the chart? Maybe the SQL is different between the two layout containers based on the query items included (or not)?

Since I'm totally throwing shots in the dark here, might as well go for another.

I had an aggregation problem recently where I had used interim query items as the basis for subsequent query expressions as you have done. The clever muppet who turns up here frequently suggested I replace reference to the interim items with full expressions referencing all package items. The reference to package items vs. query items can cause differences in SQL generation with regard to "before aggregation" vs. "after aggregation".

Good luck!

[CognosInMaine]

I don't know why the list would show you that your calculations are correct while your chart would behave differently. Are there additional elements shown in the list that are not in the chart? Maybe the SQL is different between the two layout containers based on the query items included (or not)?

Nope, same query, same data items. I built the list just to verify the numbers on the chart.

I'll look at giving your suggestion a try and see what happens.

[CognosInMaine]

Got it, had to flip things around on how they were being counted:

Cum Oper Days for Report is found by:
(running-total ([Actual Operating Days]))

And for Cum Actual Net Tons for Report:
running-total ([Actual Net Tons])

[Lynn]

cool  8)

Thanks for taking the time to post the resolution!

[CognosInMaine]

NP, if I ask a question I consider it courtesy to post an answer if I find it outside of where I posted.