4 Replies Latest reply: Nov 1, 2011 12:30 PM by Michael Solomovich

# Group By - is not grouping :(

Hi folks, I'm having problems with the below script.

Cal1:
Load Date([Submit Date], 'DD/MM/YYYY') AS [Running Date],
[Incident Number] AS [Open Cases],
0 AS [Open Changes Requests],
Date([Submit Date], 'DD/MM/YYYY') AS CalcDate,
Date([Submit Date], 'DD/MM/YYYY') AS [Date],
Date([Submit Date],'YYYYMM') as YearMonth,
Month([Submit Date]) AS MonthNr,
WeekDay([Submit Date]) AS Week,
MonthName([Submit Date]) AS [Month],
Year([Submit Date]) as [Year]
resident Crudo;

Cal2:
Count(DISTINCT [Open Cases]) as tttt
resident Cal1
GROUP BY CalcDate;

According to me what I'm specting is to have the quantity of open cases by different dates. But what I'm getting is the value 1 repeted several times on the same date times the records for each dates and so on.

Thanks in adavance for any help!!

• ###### Group By - is not grouping :(

Your CalcDate is probably not a date, but a timestamp. Thus you get lot of unique values, but formatted they look as same date.

...

Date(daystart([Submit Date]),'DD/MM/YYYY') as CalcDate,

...

this will remove the time from the timestamp (like floor(timestamp) will also do).

Hope this helps,

Stefan

• ###### Group By - is not grouping :(

daystart could work ... but maybe better is to use the function FLOOR().

...

Floor(Date([Submit Date]),'DD/MM/YYYY')) as CalcDate

...

This function floors the date to the lowest date time ...

• ###### Group By - is not grouping :(

Anita, thanks a lot but using the floor function I got something like 43465 instead a real date. I don't know if this is because the date are taking from an Excel file.