Hi all,
I have a datamodel in which i have shown the rolling 12 months data, now requiremnt is to show the rolling weeks data, can anyone please help,please find the datamodel here. i have the opendate in epoch format
table::
LOAD
NO,
OPENDATE,
floor(num(Timestamp('1970-01-01 00:00:00.000') + (OPENDATE)/60/60/24)) as CAL_OPENDATE,
STATUS,
FROM
table.qvd(qvd);
Temp:
Load
Min(CAL_OPEN_DATE) as minDate,
MonthEnd( Max(CAL_OPEN_DATE)) as maxDate
Resident table;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', -1, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
floor(num(TempDate)) AS CAL_OPEN_DATE,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
//WeekDay(TempDate) as WeekDay,
MonthName(TempDate) as MonthName,
Num(Month(TempDate)) as MonthNum
//if(month(weekend(TempDate))= month(TempDate),div(day(WeekEnd(TempDate)),7),div(day(Weekend(TempDate,-1)),7)+1)+1 as MonthWeekNumber
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
after that i have taken the bar chart for rolling 12 months as
i have taken dimension as MonthName
and in the expressiona as below
count({<Year=,Month=,
MonthName={'>=$(=Date(addmonths(Max(MonthName), -12))) <=$(=Date((Max(MonthName))))'}>} distinct NO)
Now i want for rolling 4 weeks data, how to do, can anyone please help its very urgent.
