3 Replies Latest reply on Sep 28, 2018 11:23 AM by Okechukwu Ossai

# Please i need help with this - Calculation

Good morning guys, I have an urgent problem with the project I'm working on and it's next:

In the database I have a table that we will assume is called XXX, in this table the records have a column called Operations and for each of these operations I can have between 1 to 15 records, these records are opened from another field that we will call Status, this state is a numeric field with values from 1 to 15.

There are also two other fields, one is Date and another that we will call Status_2 in which indicates if each operation is on that date is Open (Abierto), Closed (Cerrado), Returned (Devuelto) or Cancelled (Anulado).

What I need to do is count the number of records in the database by analyzing it in the following way:

For each one of the operations I need to obtain the MAXIMUM Status in a selected date range (In a selected range could have 15 records as maximum or less, I need to know which is the maximum number of Status, the range of dates for the moment is a parameter that I use as maximum date, then it would be all the information of the base until that date of that parameter). After obtaining the maximums per operation I only need to count the operations that have Status_2 with Open or Returned value and show that value.

At the moment I have this calculation:

IF { FIXED [Operations] : MAX(Status) } = [Status]

THEN { FIXED [Operations] : COUNTD( IF [Status_2] = 'Open' OR [Status_2] = 'Returned') AND DATE([Date]) <= [Parameter_MAX] THEN [Operations] END) }}

END

I hope I've been clear, please this problem is urgent, thank you very much,

I enclose a workbook with an example. I managed to get the result from the database and an Excel and in theory should give me the value 631 if I have the date range Parametro_MIN = 2016-01-01 and Parametro_MAX = 2016-12-31.

Thank you!

Robertino

• ###### 1. Re: Please i need help with this - Calculation

Hi Robertino,

I followed the logic but I only got a count of 248 operations. It is possible you are using a different dataset which produced the 631 number. I couldn't find 'Abierto' in the data, so I assumed this should be the same as 'Activo'. I have included a second worksheet called 'Result Check' which lists all the 248 operations. You can check them and let me know if there is any operation which should be there but was omitted.

Hope this helps.

Ossai

1 of 1 people found this helpful
• ###### 2. Re: Please i need help with this - Calculation

Thank you very much Okechukwu!! yeah the 248 result is correct! thanks

• ###### 3. Re: Please i need help with this - Calculation

You are welcome. Glad it helped.