Problem Performing Excel Formula on Null Value Data Cell Exported from Crosstab

[Former Member]

Hi,

I am using Design Studio 1.5 SP1.1 with BEx query as the data source to create an Analysis Application.  After exporting the crosstab data to Excel, I am having problem to perform Excel formula on the data cells which are having NULL value.

Example:

    

As there is no entry in BW infoprovider for Customer 'X' 'Feb-2015' intersection, the sales $ is showing as blank in the report.

When trying to sum the total Sales $ for Feb-2015 using formula "=C3+C4+C5", there is error.  But I am able to perform the sum using formula "=SUM(C3:C5)".

The cell's length is zero as shown in below screen shot:

    

Appreciate if anyone can assist me on this.  Thanks.

[former_member216148]

Hi..I am sending you links on SAP Design Studio. Please Check.

http://visualbi.com/blogs/design-studio/advanced/sap-design-studio-1-5-parallel-query-processing-ove...

Regards

Saad

[Former Member]

Hi Saad,

Thanks for the links but I don't see this issue being mentioned there.

[Former Member]

Hi,

I think it depends what version of Microsoft Excel you are using. In Excel 2013 this issue doesn't occur. However, making use of IFNA function could help you.

=IFNA(C2,0)+C3+C4

You can apply the formula to each cell you want. If it doesn't work try another IF(xxx) formula.

EDIT: or you can make a copy of column C and put this formula:

=IF(LEN(C2)=0,0,C2)

And make the addition with the new column.

Regards,

Bogdan

[Former Member]

Hi Bogdan,

Thanks for the idea.  In fact I am looking for the fix in Design Studio.