please find the attached Img of visualization currently i am working on. In this One i am facing small issue. When i am trying to take Year (fig1.1.year) as Dimension. All Months Are not displayesd AUG and SEP is missing. i tried to resize the length of the graph still there is no use. And, In the weeks ((fig1.2.weeks). Is there any possibility to display weeks as "Week-1.....Week-53" but not as the numbers as shown 2-2016,3-2016,........
Min(SL_DATE) as MinDate,
Max(SL_DATE) as MaxDate
// //Set script variables with min and max order dates
LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));
LET vToday = $(vMaxDate);
//Generates a single table with one field containing
//all existing dates between MinDate and MaxDate.
Date($(vMinDate) + RowNo() - 1) as TempDate
$(vMaxDate) - $(vMinDate) + 1;
//Drop MinMax table - best practice to drop tables as you go to free up memory
DROP TABLE MinMax;
//Create the Master Calendar
Week&'-'&If(Week=1 AND Month=12,Year+1, If(Match(Week,52,53) AND Month=1, Year-1, Year)) as WeekYear;
Floor(TempDate) as SL_DATE,
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
WeekDay(TempDate) as WeekDay,
'Q' & Ceil(Month(TempDate) / 3) as Quarter,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
InYearToDate(TempDate, $(vToday), 0) * -1 as CurYTDFlag,
InYearToDate(TempDate, $(vToday), -1) * -1 as LastYTDFlag,
If(DayNumberOfYear(TempDate) <= DayNumberOfYear($(vToday)), 1, 0 ) as IsInYTD,
If(DayNumberOfQuarter(TempDate) <= DayNumberOfQuarter($(vToday)), 1, 0) as IsInQTD,
If(Day(TempDate) <= Day($(vToday)), 1, 0) as IsInMTD,
If(Month(TempDate) = Month($(vToday)), 1, 0) as IsCurrentMonth,
If(Month(AddMonths(TempDate,1)) = Month($(vToday)), 1, 0) as IsLastMonth
ORDER BY TempDate ASC;
DROP FIELDS Week, MonthYear; //Created as example for UI dimension
//Drop the TempCal table as it is no longer reuired
DROP TABLE TempCal;
Solved! Go to Solution.
Your calendar script seems quite ok. Can't see any problems with it.
Your can get the WeekYear to have a W in front by simply adding it to the appropriate load column like this:
'W' & Week&'-'&If(Week=1 AND Month=12,Year+1, If(Match(Week,52,53) AND Month=1, Year-1, Year)) as WeekYear
When it comes to the missing months it must be something with your data table of sales or something with how you created you line chart. I did a test run in my environment and have no problems with missing months based on your master calendar script. Could you share a sample app to illustrate the problem?
Can you post the expression you used for both these charts you might be missing values for those months for a certain measure which is resulting in null() or zero values
Dear Mr. Petter
Thanks a lot for the Solution of weeks, But sir, here the requirement is to hide the year from Week year.
Current Week year : 1- 2016, 2-2016, 3-2016....
After your Sol: W-1-2016,W-2-2016,W-3-2016,.....
Requirement is: W-1, W-2, W-3, W-4.............