Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear QVExperts,
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 |
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.
Please suggest.
You need to use Month field, instead of SalesMonth field
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.
Thanks,
Sasi
Created a master calendar through binary load and see Sep came up:
See my response below
Hi ,
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 .
Input:
Load
Cert_No,
Date(Date#(SalesDate,'M/D/YYYY')) as SalesDate,
Month(Date#(SalesDate,'M/D/YYYY')) as SalesMonth,
Sales
Inline
[
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
];
Temp:
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;
TempCalendar:
Load
Month(AddMonths($(vMinDate), IterNo()-1)) as Month
AutoGenerate 1
While AddMonths($(vMinDate), IterNo()-1) <= $(vMaxDate);
Concatenate(Input)
Load Month as SalesMonth Resident TempCalendar
Where Not Exists(SalesMonth, Month);
Drop Table TempCalendar;
Thanks Sunny.
I am trying the same. but i am not getting 0 for Sep2015.
Can you please help me on the same qvw file
Thanks,
Sasi
You need to use Month field, instead of SalesMonth field
Thanks a lot Manish & Sunny.