How to display 0 for no records

[#1]

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.

[sunny_talwar]

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

[#3]

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

[sunny_talwar]

Created a master calendar through binary load and see Sep came up:

[sunny_talwar]

See my response below

[elakkians]

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 .

[MK_QSL]

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;

[#8]

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

[sunny_talwar · Accepted solution]

You need to use Month field, instead of SalesMonth field

[#10]

Thanks a lot Manish & Sunny.