How to Set a calculated field/Parameter For "Date Range - Hours" By Specific conditions provided

Q&A
, ,
1

Hey,

11
11829×385 52 KB

This is the current scenario of Analysis, I’ve got the data as “Outage Hours in Mins” so i’ve converted it as Hours by (Outage Mins/60). Now i need to set a calculated field/Parameter so that if i select the date filter in (Weeks,Months,Quarter) it should show me the specific Outage Hours Percentage respectively and It should only calculate by total Working hours in a “Month/week/selected between dates” (eg. 200 Hrs outage/Month in 210/Total working hrs per month - so 95% is the Outage percentage in a month) This should be Filtered in KPI.

Conditions :

  1. “#Outage Hours” Data is Perfect according to the Work Timings. (Non workings days/Hours are not calculated). so no filter is required in this field.
  2. 10 Hrs per DAY is Working Hours.[Week- 5days(50Hrs), Month 21Days(210Hrs)]
  3. Percentage value can be Approx. don’t need a accurate value.

Current Issue :
In the Provided Image above i’ve made a calculated field as (Outage Hrs/210) and the value is correct for month but if i select the dropdown filter and change it as week it would show a irrelevant percentage as my calculation field is only set to month’s Working hours.

Kindly Help me out from this situation.

Thanks

2

Hello @Shakthi, okay, I have an idea that might work with the way you have date filtering currently implemented. If this doesn’t work though, it is likely you would have to alter the date filtering from a relative date filter to some distinct date selection parameters. I’ll try and provide a work-around though to see if it works.

I’ll break this up into a few fields so we can test the results along the way. First, lets return the number of work-days within the date filtered range.
WorkDays = ifelse(extract('WD', {Date}) >= 2 AND extract('WD', {Date}) <= 6, truncDate('DD', {Date}), NULL)

Now we want to count the number of days, and multiply by the number of working hours for the days.
Number of Work Hours = count_distinct({WorkDays})*10

Now to you should be able to divide the total outage hours by the total hours to get a dynamic percentage value that changes depending on your filter. Let me know if you run into any errors while implementing this, and I can guide you further.

3

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the Quick Sight Community!