Discussion Board for collaboration on QlikView Scripting.
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!!
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,
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 ...
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.
It should be date(floor(...))