Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

4 Replies
swuehl
MVP
MVP

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

Anonymous
Not applicable
Author

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 ...

Not applicable
Author

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.

Anonymous
Not applicable
Author

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