cancel
Showing results for 
Search instead for 
Did you mean: 

How to create dimension based on count/measure

Former Member
0 Kudos

Hello Dear Friends,

I've come accross very tough situation while trying to create "dimension" in BO Webi 4.2

First of all, I need to calculate Count (activity where balance on each account is larger then 0.01 $).

So in my table 2, I've created variable "Count":

=Count([Name] Where([Balance]>0.01;distinct)

In table 3, I've created "Sum", to show how for how many "Names" per each account my above condition is true.

=If(Not(IsNull([Count]))) Then Sum([Count]) In ([Account])

And I am able to show nice and perfect in that in table 3.

However, my whole idea is to show exactly the same in table 4, but without column "Account" !!! How can I achieve that?

I've tried to use detail type object, then transforming it into dimension type object. But with no luck. I would really appreciate if you can help?

Accepted Solutions (0)

Answers (1)

Answers (1)

amitrathi239
Active Contributor
0 Kudos

use below formula in table 4 for sum of acc.

=Count([Name];All) Where([Balance]>0.01)

Former Member
0 Kudos

Hi Amit,

It won't give me 2 for each [Name], but simple 1, as per my variable [Count]

I want to show aggregated Sum of [Count] per account against [Names] even if the accounts aren't there.

Thanks

amitrathi239
Active Contributor
0 Kudos

is you data in tabular format or crosstab?

what is the formula of May/June

I have tried with cross tab and it's giving me 2.

Former Member
0 Kudos

Hi Amit,

I am using crosstab.The formula May/June is just a measure [Balance] for each month.

I've decided to change a bit my example to show exactly what I am trying to achieve:

for [Name]="Alex", I've added another account, which belongs only to Alex, and as you will notice - he has activity (Balance) in both months, that means that the count of Names in that particular account is 1.

So in my last table 4, I'd like to see the Sum of [Count] per account against [Names].

If I remove column Acc.Num. I will get for Alex 3, but I dont want 3, but the split 2 and 1.

We can create new dimension, let's call it [dim]=If([Sum for Acc]=2) Then "OK" Else "BAD". I use detail type object and then transform it to dimension like [dim] in ([Acc.Num.]). And it's still doesn't work.

Is that clear? 🙂