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.

[udit_k]

write below expression in Text Color expression of each measure:-

if(Len(Region)<=0,white())

[inam]

Hiii @udit_k 

I used below its not work.

&  also i uesd your formula but if I use if(Len(Region)<=0,white()) i am getting whole value white (direct, email + ma, organic, paid & referral)

and if i used this how i remove Total Value ? 65 i want total should be 499.  same as if 37 remove total should be 205?

[udit_k]

Hi , We can't Hide that row , but we could have workaround by using expression.

Use below expression for final calculation:-

(Sum({<Region={"=len(Region)>0"}>}Count)*sum(total<USER,Region>{<Region={"=len(Region)<1"}>} Count))
/

sum(total<USER>Count)

so here Region having null data, that row calculation is to Zero and it doesn't calculate in  total as well.

[inam]

hii @udit_k 

i am using same your formula in straight table but not getting value 

1 column :-   COUNT([PROD_TYPE])

2column :-   sum(total<PROD_TYPE,Region>{<Region={'=len(Region)<1'}>}PROD_TYPE)

3 column :- SUM(total<PROD_TYPE>PROD_TYPE)

4  column :- (Sum({<Region={'=len(Region)>0'}>}PROD_TYPE)*sum(total<PROD_TYPE,Region>{<Region=                                            {'=len(Region)<1'}>} PROD_TYPE))

5 column :-Column(1)*Column(2)/column(3)

[udit_k]

please find your corrected expression as below :-

4  column :- (Sum({<Region={'=len(Region)>0'}>}PROD_TYPE)*sum(total<PROD_TYPE,Region>{<Region=                                            {'=len(Region)<1'}>} PROD_TYPE))/sum(total<Region>PROD_TYPE)

As per below expression , you should have numeric field for calculation. here count is numeric field.

If you want to use PROD_TYPE for calculation field , you could use count(PROD_TYPE ) instead of sum(PROD_TYPE )

(Sum({<Region={"=len(Region)>0"}>}Count)*sum(total<USER,Region>{<Region={"=len(Region)<1"}>} Count))
/

sum(total<USER>Count)

[inam]

hello @udit_k 

now i used this but not get value also i used count instead of sum:-

1 column :-   =Count(PROD_TYPE) 

2column :-  =SUM(total<PROD_TYPE,Region>{<Region={"=len(Region)<1"}>}Count(PROD_TYPE)) 

3 column :-  =sum(TOTAL<PROD_TYPE>Count(PROD_TYPE))

4  column :- =(sum({<Region={'=len(Region)>0'}>}PROD_TYPE)*sum(TOTAL<PROD_TYPE,Region>{<Region=                                        {'=len(Region)<1'}>} PROD_TYPE))/sum(TOTAL<Region>PROD_TYPE)

5 column :-Column(1)*Column(2)/column(3)

[udit_k]

try to evaluate using count of(Prod_Type )in 4 column exp .

If not please could provide dummy data .

[inam]

hi @udit_k 

I tried Multiple times but not getting any value 😕 I used (sum count )but not getting answer.

please find below my snap shot and expression.

1) =Count(PROD_TYPE) 

2) =count(total<Region>{<Region={"=len(PROD_TYPE)<1"}>}Count(PROD_TYPE)) 

3) =sum(TOTAL<PROD_TYPE>Count(PROD_TYPE))

4) =(Count({<Region={'=len(Region)>0'}>}PROD_TYPE)*Count(TOTAL<PROD_TYPE,Region>{<Region=                               {'=len(Region)<1'}>}Count( PROD_TYPE)))/Count(TOTAL<Region>PROD_TYPE) 

5)   =Column(1)*Column(2)/column(3)

and also if i get proper data how i hide my unuesd column i want to show dashboard like this 

[udit_k]

Please could you correct me that values which you are showing in pivot table is not correct to count(PROD_TYPE).

It seems to me that it  will be something other than,  like sum(Count) as in example posted earlier and following those calculations.

Could you correct your expression to field other than PROD_TYPE , which is giving you correct values in pivot table.

[shree083]

Check constraint - so are you inserting allowable values? Look at the table definition to see?