null values

[inam]

Hello Community

in my data I found blank value So I want all my blank value merge into actual data.

current output:-

here I found 58 blank data in Dom holiday FIT so i want to apply below formula for each field(direct, email + ma, organic, paid & referral)

for example:-   

(16*58)/506

16 = Direct total as per the report

58 = No source data (blank data)

506 = total of direct, email + ma, organic, paid & referral

how I apply this formula in my script? I have given only 1 date ..I have  to calculate for 1 month.

[marcus_sommer]

You need to assign a real value to these NULL to be able to access these data. This might be done within the script per:

if(len(trim(PROD_TYPE)), PROD_TYPE, 'no Data') as PROD_TYPE

and then in the chart-expression something like:

count(PROD_TYPE) * count({< PROD_TYPE = {'no Data'}>} total <PRODUCT> PROD_TYPE) /
count(total <PRODUCT> PROD_TYPE)

[udit_k]

use below expressions:-

[inam]

Thank for the reply 

I am using pivot table please can you help same as pivot table . how I use this formula in pivot table

[inam]

Hello @marcus_sommer  Thank for the help .

i have used this formula & its working fine.

formula :-  =count(PROD_TYPE)+count(PROD_TYPE) * count({< PROD_TYPE = {''}>}
                     total <[Product Type]> PROD_TYPE) /count(total <[Product Type]> PROD_TYPE)

but I got null value .can you please help how I remove this  65 & 37 below values

so if I remove 65 i want total should be 499.  same as if 37 remove total should be 205

I have used backend script below

[marcus_sommer]

I'm not sure if {< PROD_TYPE = {''}>} would fetch the created empty string. You may try instead {< PROD_TYPE = {'*'}>} or {< PROD_TYPE = {"*"}>}. Personally I wouldn't create an empty string even if you could access them everywhere like you want because it's rather confusing and not the entire truth. Like mentioned specifying it as 'no Data' or 'NULL' and if there were various sources/causes including them too as information.

Beside of this what's really the aim of this view and which further views might be touched? In the above scenario it might be an alternatively to keep the missing values as NULL and to hide this NULL within the chart-option for the dimension.

Otherwise I suggest to put each single calculation into an own expression to see which one return the expected results and if then combining them step by step into further separate expressions to continue the validation if the parts are added in the right order - you may need to wrap some parts with brackets to determine the wanted order. 

[inam]

Hello @marcus_sommer 

'No data' value is coming from Raw data. i want 1st this 'no data' value is calculate according to formula and then 2nd is when the value  is calculating  correctly then remove 'No data' value. is this possible ?

I am using pivot Table .

i want to remove only 'No data' values  or 'No data' rows  after calculate according to formula. do you have any alternative solution ?

[udit_k]

The expression posted earlier is in pivot table 

[marcus_sommer]

Conclusion: no data shouldn't be displayed as rows within the chart but the numbers should be available within any total calculation? If yes, you may use expressions like those one:

normal count:
count({< PROD_TYPE -= {'no Data'}>} PROD_TYPE) 

total count (same value for all rows) + a boolean check to exclude the no data rows
count(total <[Prod Type]> PROD_TYPE) * sign(count({< PROD_TYPE -= {'no Data'}>} PROD_TYPE)) 

[inam]

hello @udit_k 

formula working fine  but i want to remove only 'No data' values  or 'No data' rows  after calculate according to formula. do you have any alternative solution ?

as your solution null value is also count as i see in your Total. how i remove null values?