-
1. Re: Fixed LOD Calc: Need to count number of days in selection
Chris ChalmersNov 29, 2018 1:01 PM (in response to Ben Perlman)
Hey Ben,
The problem is that DATEPART('day', [Call End Time]) only returns numbers between 1 and 31, so there can be at most 31 distinct values, even if your date range spans multiple months. For example, that function will return 21 for both 1/21/18 and 2/21/18, so those two dates will only be counted as a single day.
You should get accurate results by using DATETRUNC instead of DATEPART, so that the month and year stay attached to the day.
-Chris Chalmers
-
2. Re: Fixed LOD Calc: Need to count number of days in selection
Joe OppeltNov 29, 2018 1:07 PM (in response to Ben Perlman)
Do you see at the bottom of your Desktop screen you have 27 columns?
Display the header for DAY(Call...) You only have 27 unique dates in there, even though your range runs from August through November. I'll bet 3 of those dates are weekend days.
Your COUNTD is counting only what is actually there in your table. That's all it can do.
-
3. Re: Fixed LOD Calc: Need to count number of days in selection
Joe OppeltNov 29, 2018 1:14 PM (in response to Joe Oppelt)
And even though your filter is set to August, your data only starts on October 18.
-
4. Re: Fixed LOD Calc: Need to count number of days in selection
Joe OppeltNov 29, 2018 1:17 PM (in response to Joe Oppelt)
1 of 1 people found this helpfulChris makes an important point about DATEPART.
Without COUNTD, you are counting a ton of different time stamps. You could also do DATE([Call End Time]) instead of DATRTRUNC. That chops off the time portion of the date-time value. The results are equivalent.