<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body dir="auto"><div>Perhaps store sum and count for average instead of the average itself?<br><br>Sent from my iPhone</div><div><br>On Aug 5, 2015, at 1:24 PM, Julian Hyde <<a href="mailto:julianhyde@gmail.com">julianhyde@gmail.com</a>> wrote:<br><br></div><blockquote type="cite"><div><meta http-equiv="Content-Type" content="text/html charset=utf-8">Sounds basically right.<div class=""><br class=""></div><div class="">Calling it “fact_count_unit_sales” is a bit grandiose. It is just a "count(unit_sales)” measure - i.e. something we already know how to do.</div><div class=""><br class=""></div><div class="">And if unit_sales is not null then we could (and should) use count(*) — i.e. the fact_count column — instead.</div><div class=""><br class=""></div><div class=""><br class=""><div><blockquote type="cite" class=""><div class="">On Aug 5, 2015, at 5:44 AM, Matt Campbell <<a href="mailto:mcampbell@pentaho.com" class="">mcampbell@pentaho.com</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div class="WordSection1" style="page: WordSection1; font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class="">The SQL avg function will compute the mean of non NULL values. With Mondrian aggregate tables, we compute the average by taking the rolled up aggregate value and dividing by fact_count. If that rolled up value happened to include any NULL values than the aggregate table avg differs from what Mondrian computes against the fact table, since fact_count is a count of every row.<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class="">I’m thinking the way to deal with this would be to introduce a measure-specific fact_count that counts non NULL events associated with the measure. So there could be a “fact_count_unit_sales”, for example, used to compute the Avg Unit Sales price. Can anyone think of alternative approaches?<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class="">Right now, I think the only workaround for people trying to get accurate Avgs from aggregate tables when NULLs are involved is to use measure specific aggregate tables where the fact_count is calculated based on non NULL.<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div></div><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">_______________________________________________</span><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">Mondrian mailing list</span><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><a href="mailto:Mondrian@pentaho.org" style="color: rgb(149, 79, 114); text-decoration: underline; font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class="">Mondrian@pentaho.org</a><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><a href="http://lists.pentaho.org/mailman/listinfo/mondrian" style="color: rgb(149, 79, 114); text-decoration: underline; font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class="">http://lists.pentaho.org/mailman/listinfo/mondrian</a></div></blockquote></div><br class=""></div></div></blockquote><blockquote type="cite"><div><span>_______________________________________________</span><br><span>Mondrian mailing list</span><br><span><a href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a></span><br><span><a href="http://lists.pentaho.org/mailman/listinfo/mondrian">http://lists.pentaho.org/mailman/listinfo/mondrian</a></span><br></div></blockquote></body></html>