Exporting Null values as Zero

[Chad]

I am trying to export a summary where the report has many null values. I have been able to change the view to make all null values appear as a zero in the summary. However, when I export the summary to an Excel spreadsheet the fields are blank.

Is there a way to export the zero values that I have populated through the view?

Thanks,

Chad

[Data Kruncher]

Hi Chad,

As you've seen, there's an important difference between the appearance of zero values and true zero values in the summary measure fields.

Monarch is only displaying zeros; the actual value is still null, so that's what makes its way to Excel.

The way to resolve this is to create a calculated field in the Table window that will convert nulls to zero, and then use that calculated field as the summary measure.

Usually the formula look like this:

If(IsNull(MyField),0,MyField)I forget if the IsNull function is available in v8 (per your profile). If it isn't, that's alright, we can duplicate its functionality easily with:

iIf(MyField=1/0,0,Myfield)There's a mention of how Monarch treats null values in the "View" section in this piece entitled "Breaking All The Rules (http://excelwithmonarch.com/tips/breaking-all-the-rules)", which is part of the currently underway 30 Days to Become a Better Monarch Modeler (http://excelwithmonarch.com/tips/30-days-to-become-a-better-monarch-modeler) series.

HTH,
Kruncher

[Chad]

That's what I thought. I was hoping that there was a quicker way to do it. Oh well.


Thanks for your help.

[Grant Perkins]

Chad,

I think there are valid reasons for preferring a separate calculated field for this. Numeric values may be subject to auditing and having a single field and its formula to audit seems to me to be preferable to checking on a field by field basis to identify where a zero value came from. In other words an explicit formula may be more convincing than a default transformation parameter! This would be especially true, I suspect, when using the new Monarch Context facility from V10.5.

Your profile indicates you use V8 but that information may be out of date.

If you have V9 or later you could save your 'Null to zero' conversion field as a generic User Defined Function to make it generally available for future use. It would save the need for remembering the formula or having to look for it other models each time you need to cut and paste it into a new model.

HTH.


Grant