Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
anyoneplease help its very urgent
Try adding this to you MasterCalendar:
AutoNumber(weekyear(TempDate) &'|'&week(TempDate),'_WeekSerial') as WeekSerial
Then your expression can be defined as:
count({<Year=,Month=,WeekSerial={'>=$(=(Max(WeekSerial))-2) <=$(=(Max(WeekSerial)))'}>}>} distinct NO)
I hope it helps
Hi all,
i got it, but it is showing from current week data also, i dont want current week data, i want from previuos week to last 4 previous weeks data
the expression is
count({<Year=,Month=,WeekSerial={'>=$(=(Max(WeekSerial))-6) <=$(=(Max(WeekSerial))+1))'}>} distinct NO)
can anyon eplease help
Go with:
count({<Year=,Month=,WeekSerial={'>=$(=(Max(WeekSerial))-5) <=$(=(Max(WeekSerial))-1))'}>} distinct NO)
HI can anyone please help, its very urgent
Hi all,
i need the expression for last 4 weeks rolling data
and also i need to show the diffrence of count in last 2 weeks in separate column as up and down arrow,
can anyon eplease hep ,its very urgent
My bad I was throwing a few extra parenthesis on previous reply
Count({<Year=,Month=,WeekSerial={'>=$(=Max(WeekSerial)-4) <=$(=Max(WeekSerial)-1)'}>} Distinct NO)
I am getting for week 12,13,14 ,15 and 16 week data.. I want to show only 12,13,14 and 15 week data and in seperate column i need to show the diffrence of count between 15 week and 14th week . means for 15 week the no is 56 and for the 14th week the no is 50, i want to show in sepate column as 6