Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to display 0 for no records

Dear QVExperts,

   

Cert_NoSalesDateSales
1231/21/20151000
1242/18/20152500
1253/15/20153000
1264/11/20152500
1315/12/20151100
1356/13/20151750
1417/5/20151800
1508/4/20152500
15910/13/20153400
16111/1/20152950
18712/5/20153175

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.

1 Solution

Accepted Solutions
sunny_talwar

You need to use Month field, instead of SalesMonth field

Capture.PNG

View solution in original post

9 Replies
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

Not applicable
Author

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:

Capture.PNG

sunny_talwar

See my response below

elakkians
Partner - Contributor III
Partner - Contributor III

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
MVP
MVP

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;

Not applicable
Author

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

You need to use Month field, instead of SalesMonth field

Capture.PNG

Not applicable
Author

Thanks a lot Manish & Sunny.