Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
Stefan
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(...))