9 Replies Latest reply on Jul 5, 2016 3:50 AM by Ashish Chaudhari

# Calculated field to indicate if equipment is in use or not

Hi all,

I'm just not getting this, so please help.  The exam question is I need to be able to count the number of tanks in use at any one time.  I have a series of steps of a process and each step has an associated start and end time (the data is a little dodgy but forget that for now), each step is also associated with a particular tank.  What I'm trying to do, is use a calculated field to see if the "current time" is between the start and end time, then the flag is true, else the flag is false.  My thinking is that if I sum up the number of true fiags at any one stage, then I can see the number of tanks in use.  I'd like to plot how many tanks are in use on a continuous line chart.

see attached file.

• ###### 1. Re: Calculated field to indicate if equipment is in use or not

anyone have any ideas?

• ###### 2. Re: Calculated field to indicate if equipment is in use or not

Hi ,

you could use below calculation,

IF DATE('20-05-2016') >= [Step Earliest Timestamp] AND DATE('20-05-2016') <= [Step End Timestamp] THEN 'TRUE'

ELSE 'FLASE'

END

replace '20-05-2016' with TODAY() to get current date, i placed static data to get true result for at-least one scenario.

place that attribute in colours.

• ###### 3. Re: Calculated field to indicate if equipment is in use or not

Hi Lawrence,

Try this.

IF [Calc_Input_Day] >= MIN([Step Earliest Timestamp]) AND [Calc_Input_Day] <= MAX([Step End Timestamp]) THEN 'TRUE'

ELSE 'FLASE'

END

Please refer to the below screenshot and confirm the output. 3rd column is the input date which is created as TODAY()-60, so that it will show flag in your data since dates are old. Create one parameter of type date and put all the dates in it and make this input dynamic. So selected dates will be checked for the flag condition as per above code. I have used

MAX([Step End Timestamp]) since it contains multiple date. If we don't do this then there will be output for each and every date.

Let me know if that helps. Can't share the workbook since you are using old version.

Thanks and Regards,

Ashish Chaudhari

• ###### 4. Re: Calculated field to indicate if equipment is in use or not

Thanks guys, this is helpful but it only gives me the calculation for a specific date.  If i want to trend this over the full time range for each tank, how would I dynamically go through all the time ranges?

So I need to build a trend of the tank utilisation over time (a year) and the tank is either used or not but the only way I know if the tank is used is if the "current date" is between that specific step's start and end time.  Not the neatest piece of data I know.

Thanks again for the help.

• ###### 5. Re: Calculated field to indicate if equipment is in use or not

Hi Lawrance,

Please find the below image I have managed to get below output. As you know for a trend line we require one dimension (in this case tank), date field (in this case Time stamp) and measure (Unknown).

Now my question is which measure should i use (which will create a y-axis) which will help us to create trend?

-Ashish Chaudhari

1 of 1 people found this helpful
• ###### 6. Re: Calculated field to indicate if equipment is in use or not

Hi Ashish,

thanks for the efforts.  The measure would be the COUNTD of the tankID where the flag is true.

Regards,
Lawrence

• ###### 7. Re: Calculated field to indicate if equipment is in use or not

Hi Lawrence,

Please find the attached workbook below.

Please find the below screenshot. I have used count().

If you are using COUNTD() then find the below screenshot. I don't think you need this one.

Let me know if this helps in someway.

Thanks and Regards,

Ashish Chaudhari

1 of 1 people found this helpful
• ###### 8. Re: Calculated field to indicate if equipment is in use or not

Thanks Ashish,

I'll look at it - the countd should work but maybe there's something else wrong with the data.

Thanks again for all the help

• ###### 9. Re: Calculated field to indicate if equipment is in use or not

Please get back if you have any queries.

-Ashish