Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Tags (2)
4 Replies
swuehl
Not applicable

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.

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

afuchten
Not applicable

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

Not applicable

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.

Anyway, thanks for your suggestion.

mov
Not applicable

Re: Group By - is not grouping :(

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