Cumulative charts

[Binksy]

I'm blazing on with another question that I hope some of the clever forum minds of Cognoise can help me with.

The dashboard I'm building is supposed to have a chart with some cumulative data, but I'm having some trouble visualizing it. The data which the dashboard is built on is in a database, where I have very limited options of adding or adjusting columns, and the version of Cognos I'm running is 11.0.8.

My organization gets cases daily, and I want to show the cumulative amount of cases in our database over time, so these are the two columns I've been working with: CaseID and DATE.
The data type of the CaseID is Integer, so the output is shown as an amount in most visualizations, and the data type for DATE is Timestamp.
If I put these two data columns in a line chart it will show how many cases that were received on a specific date. I've been trying to find a function that I can use in calculation editor that will show cumulative data, but at this point nothing has given me my desired result.

I've been trying the "running-total" function, but it gives me insanely high values. The database I've been testing it on has 90k cases, but the results give me a perfect linear graph with values/count from 0 to 350 000 000 000 000 cases. The default property for usage is "Measure" and aggregate for this calculation is "Calculated", but changing these properties doesn't get me any closer to my desired result.

My desired result is a chart/graph that shows how many cases that arrived in the database on a specific date plus all the ones that were there from earlier, from the first case arrival to the last case arrival, with the amount of cases on the Y-axis and date on the X-axis.

Do anyone have any tips or solutions?

[MFGF]

I'm blazing on with another question that I hope some of the clever forum minds of Cognoise can help me with.

The dashboard I'm building is supposed to have a chart with some cumulative data, but I'm having some trouble visualizing it. The data which the dashboard is built on is in a database, where I have very limited options of adding or adjusting columns, and the version of Cognos I'm running is 11.0.8.

My organization gets cases daily, and I want to show the cumulative amount of cases in our database over time, so these are the two columns I've been working with: CaseID and DATE.
The data type of the CaseID is Integer, so the output is shown as an amount in most visualizations, and the data type for DATE is Timestamp.
If I put these two data columns in a line chart it will show how many cases that were received on a specific date. I've been trying to find a function that I can use in calculation editor that will show cumulative data, but at this point nothing has given me my desired result.

I've been trying the "running-total" function, but it gives me insanely high values. The database I've been testing it on has 90k cases, but the results give me a perfect linear graph with values/count from 0 to 350 000 000 000 000 cases. The default property for usage is "Measure" and aggregate for this calculation is "Calculated", but changing these properties doesn't get me any closer to my desired result.

My desired result is a chart/graph that shows how many cases that arrived in the database on a specific date plus all the ones that were there from earlier, from the first case arrival to the last case arrival, with the amount of cases on the Y-axis and date on the X-axis.

Do anyone have any tips or solutions?

Hi,

It sounds like you're trying to get counts rather than totals - is that correct? Counts of the number of cases over time? You might try the running-count function, or otherwise create a calculation with an expression of 1 (so that it contains 1 for each row) then use running-total on this? I'm assuming you were using running-total on the CaseID item itself, which would just cumulatively add the case numbers together?

Cheers!

MF.

[Binksy]

I was not aware of the running-count function, which seems to be what I was looking for!
Your assumption of me using the running-total on the CaseID item was right! Should the calculation contain more?

Also, when I'm running the new, glorious "running-count"-function, I still get a perfectly linear graph when it should be very far from linearly perfect. Do you have any theories of why this is?
And last, ideally the graph would show values all the way from 0 to (ca.) 90000 cases, but it gets clipped at 3000. Is there a way to increase the amount of items shown in my visualization?

[dougp]

My guess is you're getting 1 case per case.  You probably have row number along the x-axis when you probably want dates (y cases per day).

[cognostechie]

First of all, the CaseID should be marked as an identifier if it is joined to another table/query subject and marked as an attribute if not joined. That will tell Cognos not to go crazy and add it up.

Secondly, have only three columns in your query - Date, CaseID and like MF said, the running-count of CaseID. Plotting ther Date on x axis and the Data Item with running-count on y axis should do it.

By default, Cognos automatically determines the highest value depending on the maximum value of running-total but on some graphs, there is a setting to specify the max value.

Using a visualization instead of chart gives you more flexibility

[Binksy]

Thanks for your response people, but I think that I'm explaining my problem poorly, and the attachment option doesn't seem to be able to access the attachment upload path, so I can't post a screenshot to visualize.

First, I've learned that most visualizations in dashboard have a limit of 3000 data points, which probably means that I just must live with it! 😊

The other problem is that my visualization is that the "running-count/total" gives me a linear graph that really should not be linear. E.g., I know that at one early point in time, the database had about 15000 cases in it, but the running-count shows that it had 70 cases.

I'm missing something here, but I don't know what!