I'm sure this is something that a lot of people wish to do but I can't figure out how to do it. I have a calendar table generated as the following:
LET varMinDate = Num(Peek('ITEM_HARVEST_DATE', 0, 'ASSET')); LET varMaxDate = NUM(today()); //LET varMaxDate = Num(Peek('ITEM_HARVEST_DATE', -1, 'ASSET')); LET vToday = Num(today());
//*************** Temporary Calendar *************** TempCalendar: LOAD $(varMinDate)+IterNo()-1 AS Num ,Date($(varMinDate)+IterNo()-1) AS TempDate AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);
//*************** Master Calendar *************** CALENDAR: LOAD TempDate AS ITEM_HARVEST_DATE ,week(TempDate) AS ITEM_HARVEST_WEEK ,year(TempDate) AS ITEM_HARVEST_YEAR ,month(TempDate) AS ITEM_HARVEST_MONTH ,day(TempDate) AS ITEM_HARVEST_DAY ,weekday(TempDate) AS ITEM_HARVEST_WEEKDAY // ,applymap('Quarters_Map', num(month(TempDate)), null()) AS Quarter, ,date(monthstart(TempDate), 'MMM-YYYY') AS ITEM_HARVEST_MONTHYEAR ,week(TempDate)&'-'&Year(TempDate) AS ITEM_HARVEST_WEEKYEAR ,Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CURRENT_YTD_FLAG ,Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LAST_YTD_FLAG RESIDENT TempCalendar ORDER BY TempDate Asc;
which gives me every day from the beginning of my dataset up until today.
What I want to achieve is two barcharts (separate objects) that have this date (ITEM_HARVEST_DATE) along the x-axis and some measure on the y-axis. I want the x-axis to remain constant and include every day in the calendar even if there is no measure on the y-axis (i.e. zero values). I have tried the settings for 'show all values' and 'suppress null values' but these don't help, I am using set analysis for my data:
i.e. my y-axis measure is
=count( {<BASE={'xxy'}>} Sales)
So where I have zeros I get no bars on my chart - this is hard for comparisons sake.
Thanks very much for your suggestion. I couldn't get it to work in my example because my barchart is stacked by a dimension Category that has three types.
The attached example has text explaining how it's done. If you convert the chart to a bar chart, you'll see that it stacks fine, and still shows the date for which data is missing.
