count

building semantic layer
1

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?

alex1234 (BOB member since 2010-09-14)

2

Yes, you can define a count this way.
You shouldn’t use Where clauses in objects, as you will find they will not work in a query together, you should use Case, I have used this sort of thing to count flags, before:-

Sum(Case When TableName.FieldName = 'Yes' Then 1 Else 0)

Mak 1 :uk: (BOB member since 2005-01-06)

3

You want to be careful to understand what you’re asking for here… if you count values in the SQL you generally want to SUM them on the projection. Otherwise if you count them, you’ll get 2. Always 2. :slight_smile: Why? Because you will have 0, and 1, and that’s only two values.

The code posted by Mak uses a similar technique to sum a 0 / 1 flag on the database in order to do a count. I use the same concept all the time.

Dave Rathbun :us: (BOB member since 2002-06-06)

4

Thanks

alex1234 (BOB member since 2010-09-14)

5

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

alex1234 (BOB member since 2010-09-14)

6

My mum could have probably explained it, better ;).

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

Simply put, no :).

Create a measure object for each, required, measure column, if you are going to follow the suggested approach, as in :-

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)

I’m unsure about the exact logic, as you haven’t given too much away ;).

Mak 1 :uk: (BOB member since 2005-01-06)

7

thanks for your suggestion.
I have to gain sql code to make an object so that when i pull that object
it gives me count for all brand.
I think I have to use case statement but could not figure it out how to write.
I can get the count by defining each count for individual objects but then there will be too many objects.so just wanted to use logic .

alex1234 (BOB member since 2010-09-14)

8

Hi Alex,

I understand what you want now 8).
How about something like:-

Sum(Case When PREDICTIVE.wns_ads_model_variables.active_amer='1' And PREDICTIVE.wns_ads_model_variables.active_in='1' Or PREDICTIVE.wns_ads_model_variables.active_sb='1'Or PREDICTIVE.wns_ads_model_variables.active_cp='1' Or PREDICTIVE.wns_ads_model_variables.active_ic='1' Or PREDICTIVE.wns_ads_model_variables.active_ex='1'Or PREDICTIVE.wns_ads_model_variables.active_hi='1' Or PREDICTIVE.wns_ads_model_variables.active_cw='1' Then 1 Else 0 End)

You do not need to reference any from clause in an object for selection…

Mak 1 :uk: (BOB member since 2005-01-06)

9

Thanks for your reply,
the sql is working but it is giving me only one result. I need it in a group like
for
amer region:
active_in 20016
active_hi 01214
active_cp 1478964
active_cw 200140
active_sb 142301

The numbers on the left are count for the active members in amer region and hotel brand like in,hi,cp,cw,sb
thanks

alex1234 (BOB member since 2010-09-14)

10

You can use nested Case to build your “buckets” column:-

Case When PREDICTIVE.wns_ads_model_variables.active_amer='1' And PREDICTIVE.wns_ads_model_variables.active_in='1' Then 'active_in' 
Else Case When PREDICTIVE.wns_ads_model_variables.active_amer='1' And PREDICTIVE.wns_ads_model_variables.active_sb='1'
Then 'active_sb' Else Case When e.t.c.....End End End

Mak 1 :uk: (BOB member since 2005-01-06)

11

Thanks for your reply,
i already did that case statement to group the name of brands like
active_in, active_sb etc…
but i need count those active_in, active_sb in amer region.

Can i use multiple count in one object in universe desigerner?
thanks

alex1234 (BOB member since 2010-09-14)

12

Like:

For AMER region

name of brand----- count of mebrs
HI ---------------------20124
IN ----------------------30142
Cp--------------------- 10114

alex1234 (BOB member since 2010-09-14)

13

Well, as you have already provided logical grouping in buckets, couldn’t you, simply, count a unique table ID in your

wns_ads_model_variables

table and set the projection to Sum?

Mak 1 :uk: (BOB member since 2005-01-06)