Can you share sample workbook and explain in more details about what are expected result?
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]
Then drag Calculatio2 on to the view.
Hope this will help!
For more detail please share your twbx file.
Thanks & Regards
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.
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,
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.
1 of 1 people found this helpful
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.
The less than 7 days is a calculation based on ''date reviewed - the created date'' . It doesn't go from today so to speak.
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.
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?
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.
found an incident ref number we could possibly use.