nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Zero values not calculated / displayed in chart

jakoby84

Hi,

in my chart I have date on the x axis and for each time step (e.g. weeks) I count how many occurences of that date are registered. I attached a basic example, rebuilt with the sample database to illustrate the problem. Please ignore that the data points have different colors and that the data points do not align with the grid. What I want to illustrate is that for those weeks where there are no sales registered, there is no data point at all (instead of a data point with the value of zero!).

So how to get around that? How do I get the chart to display 0 / null values as well?

Regards and thx in advance, Jakob
(BIRT 4.2.1)

Find more posts tagged with

Comments

Jenkinsj5

Your problem is a little more complex, if your real data resembles the demo data.

If it was a matter of values that are null you could use SQL ISNULL ( check_expression , replacement_value ) to create zero values. But you don't have any values for the data at the data points.

Unless someone knows how to help you with graphing lack of values, you will need to create zero values to report on.

bricombs

Are you looking for total sales by week and need to see which weeks you have no sales or are you wanting to see weekly sales to customers and need to see which customers did not have an order for any given week.

jakoby84

<blockquote class='ipsBlockquote' data-author="'bricombs'" data-cid="114141" data-time="1360697067" data-date="12 February 2013 - 12:24 PM">
Are you looking for total sales by week and need to see which weeks you have no sales or are you wanting to see weekly sales to customers and need to see which customers did not have an order for any given week. </blockquote>
 
Well of course the report is just a rebuilt of the real data I'm working with. Just to illustrate the point. But mainly it's the latter one. So in the sample data it would be weekly sales per customer. 
 
In my real data I have log entries in a database which registers activities carried out on a website. So something like 
 
Date | Action | User 
2013-02-13 | action1 | user1 
2013-02-12 | action1 | user2 
2013-02-05 | action1 | user1 
etc. 
 
And in the chart I want to display a line for each user which shows the number of activities registered in the given time period. So I do optional Y series grouping on users (which I didn't in the sample report attached before). So my real 'Select Data' screen looks like this: 
Category (X) Series: row["LOGDATETIME"] 
Value (Y) Series: row["LOGUSER"] 
Optional Y Series Grouping: row["LOGUSER"] 
 
On the Category (X) Series I have a grouping of type DATETIME, aggregate expression 'Sum' to aggregate over the time period and on Value (Y) Series I have the aggregate function 'Count' to count the number of occurences of that user in the given time period. 
 
The main problem is that this particular 'Count' function does its job very well, the only problem is that it doesn't register anything if there is no occurence of that user.

jakoby84

well after doing some more research I encountered that it might be worth to look at crosstabs as the underlying data source instead of accessing the dataset directly. Because crosstabs give the opportunity to show emtpy rows / cells. Nevertheless, I did not yet figure out how to use the data drom a crosstab in a chart...
Update will follow, in the meanwhile of course any feedback is appreciated...

bricombs

If you give your Cross Table a name (Under General in Properties) you can then bind your Chart to the Cross Table. (In Binding for the Chart select Report Item and use the name you gave the Cross Table)

bricombs

Also take a look at the following article for how to build a report that shows zeros. You can easily then chart the data.

"Using MySQL to generate daily sales reports with filled gaps"
http://www.richnetapps.com/using-mysql-generate-daily-sales-reports-filled-gaps/