topic Re: Group By - is not grouping :( in QlikView

Group By - is not grouping :(

Tue, 01 Nov 2011 00:39:40 GMT

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:
Load CalcDate,
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 :(

swuehl — Tue, 01 Nov 2011 00:50:37 GMT

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

Try changing to your load script to something like

...

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 :(

Anonymous — Tue, 01 Nov 2011 08:45:44 GMT

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 :(

Tue, 01 Nov 2011 16:25:54 GMT

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.

Anyway, thanks for your suggestion.

Re: Group By - is not grouping :(

Anonymous — Tue, 01 Nov 2011 16:30:08 GMT

It should be date(floor(...))