0

# How to create dimension based on count/measure

Jul 28, 2017 at 08:16 AM

51

Former Member

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?

question.png (20.5 kB)

AMIT KUMAR
Jul 28, 2017 at 09:49 AM
0

use below formula in table 4 for sum of acc.

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

Show 3 Share
Former Member

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

question1.png (5.6 kB)
Former Member

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.

untitled.png (14.4 kB)
Former Member

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? :-)

question2.png (24.8 kB)
question3.png (7.7 kB)