In the above dataset, we don't have record for Sep2015.
Still Can we able to display in the chart for Sep'15 as 0.
Solved! Go to Solution.
You will need to create a master calendar and then you will be able to do it. There is a difference between Null and Misisng. Null can be shown, but missing can not. Sep'15 is completely missing from your application right now
Yeah. Sunny. But the requirement is that even if record is not there, can we able to display 0 for that particular month.
How can we achieve that, can you please guide on this.
If you are not having sep in your data it will not show . either you have to link to the master calendar or you have to auto generate the missing months then only you can show sep .
Date(Date#(SalesDate,'M/D/YYYY')) as SalesDate,
Month(Date#(SalesDate,'M/D/YYYY')) as SalesMonth,
Cert_No, SalesDate, Sales
123, 1/21/2015, 1000
124, 2/18/2015, 2500
125, 3/15/2015, 3000
126, 4/11/2015, 2500
131, 5/12/2015, 1100
135, 6/13/2015, 1750
141, 7/5/2015, 1800
150, 8/4/2015, 2500
159, 10/13/2015, 3400
161, 11/1/2015, 2950
187, 12/5/2015, 3175
Load MonthStart(Min(SalesDate)) as MinDate, MonthEnd(Max(SalesDate)) as MaxDate Resident Input;
Let vMinDate = Num(Peek('MinDate',0,'Temp'));
Let vMaxDate = Num(Peek('MaxDate',0,'Temp'));
Drop Table Temp;
Month(AddMonths($(vMinDate), IterNo()-1)) as Month
While AddMonths($(vMinDate), IterNo()-1) <= $(vMaxDate);
Load Month as SalesMonth Resident TempCalendar
Where Not Exists(SalesMonth, Month);
Drop Table TempCalendar;