Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
use below expressions:-
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
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
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.
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 ?
The expression posted earlier is in pivot table
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))
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?