1 2 Previous Next 22 Replies Latest reply on May 21, 2018 6:41 AM by Okechukwu Ossai

Division Calcs

Hi All,

I have a total number of incidents that have been reported on in the last 7 days which is 105 and i need to divide it by the total number of incidents which is 144 giving me 73%. I need the 73% to become automatic so when you filter the incident factor it changes. Currently both the total sums for incidents and reported are from 'Number of Records' with another calculations on top, so i can't really divide total number of records by total number of records - or does the filtering help tell the difference?
Thanks

• 1. Re: Division Calcs

Hi, Rebecca

Can you share sample workbook and explain in more details about what are expected result?

ZZ

• 2. Re: Division Calcs

Hi Rebecca,

If you have any date field on basis of which you want to consider last 7 days number of records then use that date field in calculated field.

Write a calculation1 : this will give you last 7 days number of records

IF DATEDIFF('day',[DATEFIELD],TODAY()) >= 1

AND DATEDIFF('day',[DATEFIELD],TODAY()) <= 7

THEN [Number of Records]

END

Write calculatio2:

sum(Calculatio1)/sum(NumberOfRecords)

Then drag Calculatio2 on to the view.

Hope this will help!

Thanks & Regards

• 3. Re: Division Calcs

Unfortunate i cant share the workbook. I can share this though if it helps?

• 4. Re: Division Calcs

If you want the percentage calculation to change when you filter, then right click on the Incident Factor filter and select 'Add to Context'.

Hope this helps.

Ossai

• 5. Re: Division Calcs

Hiya,

Thanks for you reply. I should of mentioned currently that % is a text box that i have worked out manually and update each time. So i'd like it to change based on filter selection for the two parts you divide in the photo. Thanks,

• 6. Re: Division Calcs

I understand your data is confidential. However, it's difficult to understand what the issue is without seeing a sample dummy workbook with fake data. Sometimes. it's impossible to understand let alone provide a meaningful solution by just looking at images.

• 7. Re: Division Calcs

Reading through your question again, I have one more suggestion for you to try. I'm not sure if have already done this. I've used generic field names. Please replace them with appropriate fields in your database.

Step 1: Create calculated field [# of Incidents Reported in Last 7 days]

COUNTD(IF DATEDIFF('day', [Incident Raised Date], TODAY()) >= 1 AND DATEDIFF('day', [Incident Raised Date], TODAY()) < 7 THEN [Incident Reference Number] END)

Step 2: Create calculated field [% Last 7 Days Raised Incidents]

[# of Incidents Reported in Last 7 days] / COUNTD([Incident Reference Number])

Hope this helps.

Ossai

1 of 1 people found this helpful
• 8. Re: Division Calcs

Hiya,

this is what i am trying to do in plain text with the fields if that helps to get a % at the end? Thanks

• 9. Re: Division Calcs

The less than 7 days is a calculation based on ''date reviewed - the created date'' . It doesn't go from today so to speak.

• 10. Re: Division Calcs

What does the error message say? I can see that Calculation1 is missing a numerator after THEN, before "/".  The formula should look like the generic form below.

IF A = B THEN X / Y END

Did you follow the 2 steps outlined in my previous answer? That should get you there I guess.

• 11. Re: Division Calcs

Oh that's not the actual calculation, I was trying to explain what i wanted.

I tried the above steps you gave thanks, - they work and are valid but they return 0%.

I changed the first date field to the reviewed date, as those two minus each other give me the less than 7 figure. .

• 12. Re: Division Calcs

Ok, we can fix it together by going through some questions and steps.

Why are you using [Number of records] in the Last 7 days calculation? Do you have an incident ID or Incident reference number? If you do, what is the field called?

• 13. Re: Division Calcs

Thank you.

No Incident number. I do SUM of number of records against a division, so for instance - the blue circle with 105 is the total of number of records for those divisions, and i divide it by the total number of incidents in the top left which will = the % in the second circle (73)

The figures for the division (46, 3, 14 etc) are made up from a calculation which is placed on the filters shelf to show me the amount of those days that were reported within 7 days.

which is this

If [number of days diff] < 7 then 'Less' END

and number of days diff is

[Incident.CreatedDate] - [Incident.IncidentDate]

// this is the difference between dates

I would like the reported % to be automatic rather than me manually adding it, currently that is a text box and i work out the maths then add it.

• 14. Re: Division Calcs

found an incident ref number we could possibly use.

1 2 Previous Next