topic Claculating Days between dates then getting Average in QlikView

Claculating Days between dates then getting Average

Thu, 05 Apr 2012 21:07:59 GMT

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

swuehl — Thu, 05 Apr 2012 22:36:19 GMT

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.

Re: Claculating Days between dates then getting Average

Thu, 05 Apr 2012 22:50:46 GMT

I have attached an example of the out put for both sheet objects. Both are charts, straight tables. Thanks for assisting.

Message was edited by: Sean Smith

Re: Claculating Days between dates then getting Average

Thu, 05 Apr 2012 22:56:56 GMT

Why dont you work out the difference between your dates in your lkoading script then you just need to average them out in your table box.

Claculating Days between dates then getting Average

swuehl — Thu, 05 Apr 2012 23:20:34 GMT

Sean,

if I use the data from your output sample as input, I get the same result for detail as for summary when using avg(ClearingDate-Date), i.e. 26.6 days. So I believe the issue is due to something that is hidden in your data model.

Could you upload you app or your input data?

Claculating Days between dates then getting Average

Anonymous — Fri, 06 Apr 2012 11:36:56 GMT

Hi Sean,

For every document you have a Clearing date and an Invoice date, so that works fine.

When you drop your Document you have multiple Clearing and invoice dates, so it makes sense to me that you can not use the same expresion.

The easies solution is to calculate the difference for every document in your script.

Load ..

ClearingDate,

Date,

(ClearingDate-Date) as DaysToPay,

From ..

This way you have the days to pay for all your documents which makes it a lot easier to work with.

Hope this helps,

Good Luck,

Dennis.