Cross Table NULL Values

[Lukas Egrt]

Hi guys, I'm currently solving this problem:

 

 

 

 

Fruit

Sales 2017

Sales 2018

Variance

 

 

Apples

15

20

5

 

 

Carrots

-

9

- (wrong)

 

 

Melons

12

0

-12

 

 

 

 

Sales = SN(Sum([sales]),0) as [sales]

Variance = Sales 2018 - Sales 2017

 

Carrots were not sold in 2017, therefore crosstable gives value "-".

Every other operation with carrot is therefore corrupted, if I calculate within its Sales 2017.

The main goal Im trying to achieve is to replace the values of Carrot Sales in 2017 with 0, so the variance is calculated correctly.

Editing the source data is not possible due to its complexity and size.

There are some similar topics in this forum, but without any solution or advice pointing to the mean.

Do you have some idea about some workaround

Thanks a lot!

[Richard Pobi]

Use the below Case Statement expression to create a calculated column (eg) Cal_Sales_2017 and use it. Variance = Sales 2018 - Cal_Sales_2017.

 

case

when [sales 2017] is null Then 0

Else [sales 2017]

End

[Lukas Egrt]

Hi @richierich,

 

thank you for your comment, but it seems that this workaround is not working, as the cross-table does not consider the parameter Carrots in 2017 at all, because its missing in the source.

 

Is there any other workaround which could be applied here

 

Thanks