Skip to Content
avatar image
Former Member

How to create dimension based on count/measure

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jul 28, 2017 at 09:49 AM

    use below formula in table 4 for sum of acc.

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

    Add comment
    10|10000 characters needed characters exceeded

    • 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)