Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Re: How to display 0 for no records

You need to use Month field, instead of SalesMonth field

Capture.PNG

9 Replies

Re: How to display 0 for no records

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

Re: How to display 0 for no records

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

Re: How to display 0 for no records

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

Capture.PNG

Re: How to display 0 for no records

See my response below

Highlighted
Partner
Partner

Re: How to display 0 for no records

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 .

MVP
MVP

Re: How to display 0 for no records

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

Re: How to display 0 for no records

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

Re: How to display 0 for no records

You need to use Month field, instead of SalesMonth field

Capture.PNG

Not applicable

Re: How to display 0 for no records

Thanks a lot Manish & Sunny.