Calculating the MEAN and STDEV in Birst

Hi,

I'm seeing an issue when trying to apply MEAN and STDEV in a visualizer chart in Birst.

I'm using the following BQL to calculate the MEAN but somehow Birst is showing the wrong MEAN value

stat(avg,1,SELECT [Time.Date] ,[OrderDate: Revenue] FROM [ALL])

For the set of data I have I know the MEAN is 3595.96 and when I pull the measure and choose Avg as the aggregation I get the same value which is correct but when I try to use the above BQL to add MEAN to a chart or even to table Birst shows a different MEAN 3,239.01

Same issue is happening with STDEV.

Any idea why Birst is not showing the correct MEAN when using the BQL stat(avg,1,SELECT [Time.Date] ,[OrderDate: Revenue] FROM [ALL]) ?

And if this BQL statement is wrong what is the correct statement ?

Thank you

Find more posts tagged with

Comments

Legacy Contributor

You would want to include a unique identifier column in the STAT, otherwise it's summing by date and then averaging it, which is not going to be the same as just using Avg as the aggregation. Assuming you want the "per order" mean, you would want to include something like Order Number in your STAT, like:

stat(avg,1,SELECT [Time.Date] ,[OrderDate: Revenue], [Order.Order Number] FROM [ALL])

Legacy Contributor

Hello Cindy, what if I only have a table with dates and revenue no other attributes and I want to add the chart line for the MEAN it is not giving me the correct average ?

Legacy Contributor

Well, it's hard to say without seeing the actual BQL query, but perhaps you've got filters in the report? For example, if you are filtering the report to a specific date range and not manually adding that same filtering WHERE clause into your STAT then your results will be different.

Copyright © 2025 Infor. All rights reserved.