Jump to content

How to calculate the average between two dates for every item in the table?

Andreas Komocar

Recommended Posts

I have a table with multiple values for each material. I need to calculate the average between two dates(user deined, and i know how to make it filter) for each material in a table. I dont know where to start and how to do it. I have attached a screenshot so you can view few lines of my data. Any help is appreciated. Thank you

Link to comment
Share on other sites

There could be different ways to do this, depending on what you want to display.

I am assuming you want a cross table with the results.

One solution could be to use document properties and a calculated column: 

Create a text area to select a start date and an end date, to be stored into document properties startDate and endDate.

Then calculate a column called e.g. [included] as:

Days(DateDiff([Date],Date('${startDate}')))>=0 and Days(DateDiff([Date],Date('${endDate}')))<=0

Create a cross table, in the Data tab choose Limit data using expression and set it to [included]=True

and set the vertical axis to the Material and the cell value to Avg([Date]) 

An alternative solution (simpler if you don't mind filtering out the rest of the data) would be to use filters.

Here you select dates by adding a text area with the Date filter.

Then in your cross table Data tab, you choose Limit data using filterings.

Attached a sample dxp with the two alternative suggestions.

Note that when importing your data I needed to choose a different 'culture' than en-GB (my default) to make sure Value was read as Real, given the comma decimal separator.  Make sure Date is of type Date and not String. 

Link to comment
Share on other sites

I am not sure I can find an older version to try, I am on 12.1.1. The description should be enough to reproduce hopefully.

I am going to attach a couple of screen shots. (The 'Available Date Range' in the text area is simply a couple of calculated values, Min([Date]) and Max([Date]) but not strictly part of the solution).

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...