Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i have seen this is a much posted about topic, but i still can't figure out from the existing discussions how to do it for my own data.
Essentially I have many rows of transaction data and I need this displayed by week on a bar chart, however there are some weeks where there are no transactions. This causes an issue as I need to still display those weeks, but with a zero value. I've read the 'Generating Missing Data.pdf' as well, but can't apply it to my own case as I'm a bit of a beginner!
As you can see, weeks commecning 16/12, 23/12 and 30/12 are absent in the chart below, but i need to see them
The script I use for my master calendar is as follows. Any help would be very much appreciated
Calendar:
LET vDateMin = Num(MakeDate(2010,7,16));
LET vDateMax = Floor(MonthEnd(Today()));
LET vDateToday = Num(Today());
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Calendar:
LOAD
Date(TempDate) AS CalendarDate,
// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth,
WeekDay(TempDate) AS CalendarDayName,
Week(TempDate) AS CalendarWeekOfYear,
Month(TempDate) AS CalendarMonthName,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
Year(TempDate) AS CalendarYear,
// Calendar Date Names
WeekName(TempDate) as CalendarWeekNumberAndYear,
MonthName(TempDate) as CalendarMonthAndYear,
QuarterName(TempDate) as CalendarQuarterMonthsAndYear,
// Start Dates
DayStart(TempDate) as CalendarDayStart,
WeekStart(TempDate) as CalendarWeekStart,
MonthStart(TempDate) as CalendarMonthStart,
QuarterStart(TempDate) as CalendarQuarterStart,
YearStart(TempDate) as CalendarYearStart,
// End Dates
DayEnd(TempDate) as CalendarDayEnd,
WeekEnd(TempDate) as CalendarWeekEnd,
MonthEnd(TempDate) as CalendarMonthEnd,
QuarterEnd(TempDate) as CalendarQuarterEnd,
YearEnd(TempDate) as CalendarYearEnd,
// Combo Date Examples
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,
'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
Hi,
In chart properties, Go to Presentation Tab >> Disable the condition "Suppress Zero values"
Also in DImensions tab >> Disable Show All Values
CHeck if this helps!
Sorry, i should also have said I only want the last 6 weeks of data.
Have played around with those settings for hours, and nothing works. The only thing which comes close, is ticking 'show all values', and having 'supress zero values' unticked, but this overrides the 6 week set analysis I am trying to do. Apart from that, it's perfect. I just need to ignore everything before 6 weeks ago and after today.
If there was a 'last' option on the dimension limits, this would be so easy!
Can you post a sample app with your master calendar and example data? It will be easier to help you if you do.
Hi Alex,
I recommend you to create a resident table of your calendar only with Date field, Week field.
Table:
Load Date, Week, resident Calendar;
Then, you should do a left join of your fact table by date field:
left join(Table)
load * resident [Fact Table];
Then use limit in the chart properties
hope this helps.
Regards
Hi,
Please see the attached example which uses calculate dimension to restrict the dates.
Play around with the vNumOfDays variable and see how it changes the bottom-right chart.
It would be really hard for me to provide sample data, is there any point in me providing the app with no data loaded?
The simplest solution, (although not ideal), would be to only load in the last 6 weeks date in the master calendar.
Currently my minimum date in the master calendar is expressed as follows
LET vDateMin = Num(MakeDate(2010,7,16));
How would i change this to be the first Monday of the week, 6 full weeks ago?
LET vDateMin = Num(WeekStart(today(),-6));
thanks very much