I have searched over and over but can't seem to find the answer i am looking for. Trying to calculate the last 7 days of case in covid-19 to compare to the previous 7 days. Not sure how to write the LOD.
Each day has the total of cases for that day. I want to sum the last 7 days. And then another calculation for the previous 7 days so i can look at the week over week comparisons.
Help appreciated,
Jeff
Hey Jeff,
We can do this using a fixed LOD that uses a datediff calculation. Basically what we want to do is figure out the maximum date in our data (6/4/20), then do a date diff. If it's the past 7 days, we will sum up the cases and if not, we will yield a 0. Then we will used a Fixed LOD to sum up all of those to ensure that it remains the same regardless of our level of detail. Then we will do the same thing with the previous week. Start with this calc:
@Current 7 Days
{ FIXED : SUM(
IF DATEDIFF('day', [Date Rep], { FIXED : MAX([Date Rep])}) <= 6
THEN [Cases]
ELSE 0
END
)}
This will sum the up the cases for the max date and the 6 days prior. Now lets do the same thing with the previous week - or date diff between 7 and 13:
@Previous 7 Days
{ FIXED : SUM(
IF DATEDIFF('day', [Date Rep], { FIXED : MAX([Date Rep])}) >= 7
AND DATEDIFF('day', [Date Rep], { FIXED : MAX([Date Rep])}) <= 13
THEN [Cases]
ELSE 0
END
)}
Now you can add those to the view and they will be in separate columns and very easy to compare.
You can even pull off the date and Daily Cases:
I should be sure to mention that this is fixed on the entire data set. So if you want to do something for each country, you'll have to modify your fixed LOD to be fixed on the Country.
Attached is a workbook. Let me know if this helps
Thanks,
Kevin
