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

    Division Calcs

    Rebecca Roland

      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
          Zhouyi Zhang

          Hi, Rebecca

           

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

           

          ZZ

          • 2. Re: Division Calcs
            Madhuri Singh

            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!

             

            For more detail please share your twbx file.

             

            Thanks & Regards

            Madhuri

            • 3. Re: Division Calcs
              Rebecca Roland

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

               

              • 4. Re: Division Calcs
                Okechukwu Ossai

                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
                  Rebecca Roland

                  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
                    Okechukwu Ossai

                    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
                      Okechukwu Ossai

                      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
                        Rebecca Roland

                        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
                          Rebecca Roland

                          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
                            Okechukwu Ossai

                            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
                              Rebecca Roland

                              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
                                Okechukwu Ossai

                                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
                                  Rebecca Roland

                                  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
                                    Rebecca Roland

                                    found an incident ref number we could possibly use.

                                    1 2 Previous Next