Count flags

[Former Member]

Hi all,

While defining count in an object, do I have to make the object as a measure ?

I have columns with flags 0 or 1. So i need to count all 1s while building report in webi.

Can i define the object as dimesion and count the object with column as 1? or simple define the object as measure and select count in properties?

and keep where conditon to count all 1s?

[arijit_das]

You can use the formula in the webi report as below:

=Sum(ToNumber([Flag]))
 
Or
 
=Count([Flag];All) Where ([Flag]="1")
 

[Former Member]

I have three divison x,y, z

I have 7 brand of hotel (a,b,c,d,e,f,g,h)

All divison and brand are flag columns (0 or 1).

I need to generate report for x divison and all brands and similar for all other

divison.

Basically I need to write a sql to count number of member in one divison x

and all brands in that divison.

i tried writing this sql in universe desinger but it does not work in report

I have defined this sql in an object in universe. Am i doing right?

select

sum(case WHEN PREDICTIVE.wns_ads_model_variables.active_amer='1' and PREDICTIVE.wns_ads_model_variables.active_in='1' THEN 1 else 0 end),

sum(case WHEN PREDICTIVE.wns_ads_model_variables.active_amer='1' and PREDICTIVE.wns_ads_model_variables.active_sb='1' THEN 1 else 0 end),

sum(case WHEN PREDICTIVE.wns_ads_model_variables.active_amer='1' and PREDICTIVE.wns_ads_model_variables.active_cp='1' THEN 1 else 0 end),

sum(case WHEN PREDICTIVE.wns_ads_model_variables.active_amer='1'and PREDICTIVE.wns_ads_model_variables.active_ic='1' THEN 1 else 0 end),

sum(case WHEN PREDICTIVE.wns_ads_model_variables.active_amer='1' and PREDICTIVE.wns_ads_model_variables.active_ex='1' THEN 1 else 0 end),

sum(case WHEN PREDICTIVE.wns_ads_model_variables.active_amer='1' and PREDICTIVE.wns_ads_model_variables.active_hi='1'THEN 1 else 0 end),

sum(case WHEN PREDICTIVE.wns_ads_model_variables.active_amer='1'and PREDICTIVE.wns_ads_model_variables.active_cw='1'THEN 1 else 0 end)

FROM PREDICTIVE.wns_ads_model_variables

help me plz

[Former Member]

Hi Alex,

you can create a measeure in universe using conditional count as shown below to achieve teh result

count(case when col1=1 then 1 else null end)

Thanks

Bilahari