7 Replies Latest reply on Mar 25, 2016 12:51 PM by Staci Langston

# Comparing Weekday/Weekend Records, Need to Normalize

Hello,

I am trying to compare crimes that occur during the weekend against the weekday.  My issue is that the records aren't taking into consideration that during the weekday events there are more days then during the weekend.  I need a way to "normalize" the data somehow so that the comparison can be accurate.  It seems like it should be simple to do, but I'm not sure how to start.  I'm not sure if that fully explains the question.

• ###### 1. Re: Comparing Weekday/Weekend Records, Need to Normalize

Please exaplin more Wat do mean by  "records aren't taking into consideration" :

May be you can comapre  month on month basis (First 15 days crime rate to next 15 days crime rate)

and Post workbook in Tableau 9.2 twbx format we can help you out.

• ###### 2. Re: Comparing Weekday/Weekend Records, Need to Normalize

Hi Staci,

So my understanding of normalize in this situation is to divide the number of crimes committed in a period by the number of days in the period. I can think of two different ways to do this depending on you data structure. So first do you have a record for every single day in the time period is so then either method would work. If there are missing days in your data set is it because a crime was not recorded that day or you are lacking that information.

If a gap is because no crime was recorded then you need to divide by the number of days in the date range not by the count. If a gap is because you have spotty records you would need to divide by the count of the days in your data set.

Gaps because no crime was committed would be the hardest to deal with for spotty records or a complete listing I would set a calc to count each day type below is the example for weekdays. change the > and < symbols to = replace AND with OR for the weekend version. Sum this up for the number of days.

Weekday Count

IF DATEPART('weekday',[Order Date])>1

AND DATEPART('weekday',[Order Date])<7

THEN 1

ELSE 0

END

For the date range things get trickier the best approximation I can think of would be finding the number of days between the start and end date with DateDiff using the Mix and Max of your date field then multiplying by .714 because 5/7 to get a count on the number of weekdays.

Thanks,

Kent

• ###### 3. Re: Comparing Weekday/Weekend Records, Need to Normalize

Thanks Kent,

I am new to Tableau, as in I've had it for about two weeks.  So if possible, could I get a step by step to try this out?

• ###### 4. Re: Comparing Weekday/Weekend Records, Need to Normalize

Hi Staci,

Unless I had and example of your data set I can't exactly match what you would need. Attached is an example of calculating the avg number of orders by day type which might be close to what you need. Take a look at the calculations and recreate it another workbook to get a better understanding. In this example I am using CountD on the date field to count the days and using another calculation to split the dates into Weekday or Weekend groups.

Thanks,

Kent

• ###### 5. Re: Comparing Weekday/Weekend Records, Need to Normalize

I've attached my little graph's.  I understand the concept of the calculation, but my days of the week are written out I don't know if that's different or anything.

• ###### 6. Re: Comparing Weekday/Weekend Records, Need to Normalize

Hi Staci,

So given your data structure to try and adjust for the lower frequency of weekends I think the easiest thing to do is multiply the weekend records by 2.5. In the attached workbook I have made an updated version of the workbook you attached.

Thanks,

Kent

• ###### 7. Re: Comparing Weekday/Weekend Records, Need to Normalize

That makes sense, and is pretty easy.  Thank you so much for your help!

Thank you,

Staci