2 Replies Latest reply on Sep 28, 2018 11:27 AM by Hari Ankem

# Trouble with 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: Trouble with calculation

Robert,

If you want the max status for the operation within a specific date range, you have to include date dimension in your fixed function.

Can you see if this works for you?

IF { FIXED [Operations],[Date]>=[Parameter_MIN], [Date]<=[Parameter_MAX] : MAX([Status]) } = [Status]

and ([Status_2]='Abierto' or [Status_2]='Devuelto')

THEN ([Status])

END

• ###### 2. Re: Trouble with calculation

I am not sure whether the number 631 you are expecting is really right.

All I did above was to add the date filter as shown above to context, and filtered the data with the maximum status for each operation.