handling null values

[Anonymous]

Hi,

As in screen shot I have data like this after I made some calculations. But what my requirement is in between 02/13/2015 and 02/21/2015 dates have been missed. I want to display '0' where dates have been missed.

like this i want to display.

02/13/2015  106,237  

02/14/2015    0

02/15/2015     0

02/16/2015     0

02/17/2015     0

02/18/2015

02/19/2015

.

.

02/21/2015    40.195

Pls help me on this.

Thanks

[sunny_talwar]

This is possible once you add a MasterCalendar which will have all the dates for you. Look here for the MasterCalendar Script: Creating A Master Calendar

HTH

Best,

Sunny

[amit_saini]

Kumar,

Try:

If(ISNULL(Date), 0, Date) as Calculates dimension.

Thanks,
AS

[MayilVahanan]

HI

Do you have all dates?

If yes,  Uncheck Suppress Zero values in presentation tab.

If no, then create Master Calendar for all dates and follow the above steps.

[MK_QSL]

Temp:
Load
  Date(Date#(Date,'MM/DD/YYYY')) as Date,
  Value
Inline
[
  Date, Value
  02/13/2015, 106273
  02/21/2015, 40195
];


MinMaxDate:
Load min(Date) as MinDate, Max(Date) as MaxDate Resident Temp;


Let vMinDate = Num(Peek('MinDate',0,'MinMaxDate'));
Let vMaxDate = Num(Peek('MaxDate',0,'MinMaxDate'));


Drop Table MinMaxDate;


Join (Temp)
Load
  Date($(vMinDate) + IterNo() - 1) as Date
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);


NoConcatenate
Final:
Load
  Date,
  If(IsNull(Value) or Len(Trim(Value))=0, 0, Value) as Value
Resident Temp
Order By Date;


Drop Table Temp;

[Anonymous]

I have all dates in time dimension.

[MK_QSL]

Then untick suppress when value is null in dimension tab for your date field

[Anonymous]

Hi,

I have uncheck in both dimension and presentation tab as well. But still it is displaying the same results.

In presentation tab

In Dimension Tab

[sunny_talwar]

Try unchecking 'Suppress Missing' on the presentation tab also.

[#10]

try this If(len(Trim(Date))=0, 0, Date) as Calculated dimension. Deepak