5 Replies Latest reply: Apr 6, 2012 7:36 AM by Dennis Hoogenboom RSS

    Claculating Days between dates then getting Average

      I have 2 sheet objects, 1 summary and 1 detail showing the difference between a billing date and when it was paid.   On the detail sheet, I am calculating the difference between 2 dates like this, (ClearingDate - Date) and it works, and I am getting the average like this, Avg(ClearingDate - Date) and it works.  On the summary sheet, let's assume there are 10 invoices, the difference in days in calculated the same as above and it works, however trying to get the average the same way I am not getting the correct number, it is off by several days.  Any suggestion on how I should calculate the average when summarizing multiple customer invoices?  Thanks, 
        • Re: Claculating Days between dates then getting Average
          Stefan Wühl

          Can you tell us a bit more about your setting (data model, chart objects - dimensions and expressions)? Best would be to post a small sample that demonstrates your issue.

           

          In your details sheet object (straight table?), I assume you are showing the details on invoice level (1 invoice per row), and 1 invoice has 1 ClearingDate and 1 Date. Thus you can use (ClearingDate-Date) and you get an unambiguous answer. But calculating the average should give the identical result then? This I don't understand yet.

           

          In your summary sheet object, if you aggregate over several invoices, (ClearingDate-Date) shouldn't return an unambiguous result (unless the difference is the same for all invoices), I believe you would need to use some aggregation here (like an average). It seems you don't, so I am probably way off in my assumptions.