“I have a dataset as below. To calculate the % of downtime of a machine, I sum the ‘inoperancia’ column, and then I check the time that the machine should be operating during the day (‘tempoobjetivo’), and then I divide the ‘inoperancia’ by the ‘tempoobjetivo’. In my dataset, each row is an occurrence, so I have ‘tempoobjetivo’ duplicated because, in each occurrence, I will state the ‘tempoobjetivo’ of that equipment on that day. I created the following formula to calculate it, and it is working. The problem is when I group by the offender of that occurrence, it only considers the ‘tempoobjetivo’ where the offender was that specific one. However, I want the ‘tempoobjetivo’ of all offenders and understand the representativeness of that specific offender. The offender is the category of my occurrence.”
My sumover calc to define the ‘TempoObjetivo’: (sum(maxOver(tempoobjetivo, [date, {serie}], PRE_AGG)/countOver(tempoobjetivo, [date, {serie}], PRE_AGG)))
My % calc: sum(inoperancia)/TempoObjetivo
My real result:
I need to know which numbers add up to 3.28%. For example: 1% + 2% + 0.28%… And not in the current form. Below are some fictitious data:
The intent here is the numerator is calculated with the partition offender, so it is computed at visual record level, and the denominator is kept common for overall calculation, so we use sumOver without a partition.
If this does not work, kindly create a sample analysis with the calculation and sample data in the Arena, that will help us to help you resolve this.
Here is the link to the dashboard in Arena: Test
The correct result is 71.47%. In the tables where it is grouped by offender and by cdocorab, I need to know the part that adds up to 71.47%, considering that at the moment, there are values even higher than that. One of the problems is that according to my dataset, when I filter an offender, it considers the values of the ‘tempoobjetivo’ column only where there is the specific offender. However, I want to consider the ‘tempoobjetivo’ of all offenders or all cdocorab. I want to filter the ‘tempoobjetivo’ only in columns like ‘serie’. I thought about restructuring my dataset by separating the ‘tempoobjetivo’ from the other columns, and then at the time of filtering, I would filter the offender and a ‘null’ value to consider the ‘tempoobjetivo’. However, I don’t want to show this option to the user and keep it marked as default, but I think it is not possible.
It worked well with other filters, but when I filter the “ofensor” column, it still considers the ‘tempoobjetivo’ only where that column is present, and not everything. In my real dataset, I have other columns that I would like to filter, and it worked very well. The only problem is when I filter the ‘ofensor’ or cdocorab column, because in the dataset, the ‘tempoobjetivo’ column appears in places where there are no values in ofensor and cdocorab. Is there a solution?
@Jesse I found here in the community a solution you developed to ignore a specific filter in a calculated field. However, in my real case, I have about 300 different values that can appear in the filter, so I don’t know if it’s feasible to manually put this in a parameter (from what I understand). Additionally, I would like it to be multiselect; it seems that in your solution it is only a list. Is it applicable to my case that @prantika_sinha has been helping me with?
Unfortunately, when I filter by date or serie, this field does not meet the conditions. I would still like to be able to filter by date and serie; it is only the ofensor and cdocorab fields that I want to not impact the calculation of tempoobjetivo . Example:
Even if I filter by serie , I want the sum of tempoobjetivo for the specific serie . However, when filtering the ofensor column, I want the sum of tempoobjetivo to be for that specific serie but considering all values of ofensor . As I mentioned, in my real dataset there are other fields, and the previous solution worked very well. The problem is when I filter ofensor and cdocorab . I’m sorry for so many questions, I’m still learning, thank you very much for all the support."
Have updated the arena dashboard further. The updated field validated with date and serie filters but the numbers with ofensor filter looked a bit ambiguous. kindly check out the changes done.
