Skip to Content
avatar image
Former Member

SUm Measures based on Same dimension Value

Hello all,

I have looked for an answer to this, but not getting what I am looking for.

this is an example of data

  Dimens1    Dimens2       Measure1     Measure2     Measure3
   a             b               1            2            3
   a             b               2            3            3
   a             b               1            4            1
   c             d               2            4            5
   c             d               3            2            1
   c             d               2            2            4


What I want is:

  Dimens1    Dimens2       Measure1     Measure2     Measure3
   a             b               4            9          7
   c             d               7            8          10

I have tried using 

=Sum(Measure1) forEach(Dimens1)

Do I have to do something like this for each Measure?

It didnt make any changes. Could anybody help me achieve this?

Thanks in advance,

Niraj

Edited by: NirajK on Mar 15, 2011 10:04 PM

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • avatar image
    Former Member
    Mar 15, 2011 at 10:23 PM

    What kind of aggregation did you define for your measure objects in your universe? Is it database delegated?

    Regards,

    Stratos

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Stratos,

      Thanks for the reply!!!

      I am not sure how the Measure objects were defined in the Universe, can I check in the report panel anywhere. I dont have access to the Universe, and getting an answer from backend developers may take some time due to the process of submitiing a request.

      Maybe there are a couple of formulas i could try before having to get any help from the backend Developers.

      Appreciate any help/advice.

      Thanks,

      Niraj

  • avatar image
    Former Member
    Mar 16, 2011 at 04:17 AM

    Hi Niraj,

    It is Universe level problem, make the objects as measures

    Double click on Measure1 object---- >Properties tab --->Select measure button -


    > and function SUM

    Same for Measure2, Measure3.

    if it is not solved

    create a variable in report level (webi rich client, webi, deski):

    create a variable -


    > Name: Measure10 -


    >Formula: Sum(Measure1)

    Same for Measure2, Measure3.

    Aggregation not occured for Dimension where dimension in Universe, if you make dimension in universe level make sure variable for convert dimension to measure in report level.

    All the best,

    Praveen

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 16, 2011 at 04:57 AM

    Hi,

    In Web Intelligence:

    First Create three variables like

    variable 1. Give name as Measure1 and enter =Sum([Mea 1]) ForEach([Dim 1]) in formula field 
    	variable 2. Give name as Measure2 and enter =Sum([Mea 2]) ForEach([Dim 1]) in formula field
    	variable 3. Give name as Measure3 and enter =Sum([Mea 3]) ForEach([Dim 1]) in formula field

    Now drop dimentions and variables - (Dimension1, Dimension2 ) and (Measure1, Measure2, Measure3) in WebI report

    or

    In Universe Designer:

    Create Derived table with this sql code

    select sum(Try."Mea-1") as Measure1, sum(Try."Mea-2") as Measure2, sum(Try."Mea-3") as Measur3, Try."Dim-1" as Dimension1, Try."Dim-2" as Dimension2 from Try group by Try."Dim-2",Try."Dim-1"

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 16, 2011 at 03:08 PM

    are your measures 1,2, and 3 blue? Then they are dimensions not measures. It has to be corrected in Universe.

    Add comment
    10|10000 characters needed characters exceeded