Sum of Variables

[Former Member]

Hi all,

I am developing a report that have sections by profit centers.. and its subtotals.

I created 2 new columns aside subtotals and I filled with 2 variables:

1-) =If (Sum([Main].[Montante]*[Perc].[Montante])) > 0 Then (Sum([Main].[Montante]*[Perc].[Montante])) Else 0

2-) =If (Sum([Main].[Montante]*[Perc].[Montante])) < 0 Then (Sum([Main].[Montante]*[Perc].[Montante])) Else 0

I need to sum those values and store on another variable that I will use soon. But I have a problem: Webi doesn't allow to sum by itself, so I did it manually and the result is not according to the real sum.

Does anybody has any idea about how to I can do this?

Thanks in advance.

[Former Member · Accepted solution]

Hi, could you please clarify what you mean by:

"Webi doesn't allow to sum by itself, so I did it manually and the result is not according to the real sum."

I'm not sure what you're trying to do, but you may need your end sum to use "ForEach [[Profit Centre]]" if you're going to use the sum in a block that doesn't have profit centre in it.

DG

[Former Member · Accepted solution]

Hi David,

I Mean that that button to sum is deactivated, and I needed to write the formula.

ForEach doesnt work in this case because I have already the profit center dimension in the block.

To clarify more, I had the sections as below situation:

-

---

Company | Profit Center | Amount

XXXXXX | YYYYYYY | 11111,11

XXXXXX | YYYYYYY | 11111,11

Subtotal . . . . . . . . . . | 22222,22

-

---

I developed the following:

-

---

Company | Profit Center | Amount | If Positive, Amount here | Else (Negative) Amount Here

-

---

Then now I have:

Company | Profit Center | Amount | If Positive, Amount here | Else (Negative) Amount Here

XXXXXX | YYYYYYY | -33333,33 |

XXXXXX | YYYYYYY | -11111,11 |

Subtotal . . . . . . . . . . | - 44444,44 | . . . . . . . . . . . . . . . . . . . | -44444,44

Company | Profit Center | Amount | If Positive, Amount here | Else (Negative) Amount Here

XXXXXX | YYYYYYY | 11111,11 |

XXXXXX | YYYYYYY | 11111,11 |

Subtotal . . . . . . . . . . | 22222,22 | . . . . . 22222,22 . . . . . . . | . . . . . . . . . . . . . . .

Company | Profit Center | Amount | If Positive, Amount here | Else (Negative) Amount Here

XXXXXX | YYYYYYY | 22222,22 |

XXXXXX | YYYYYYY | 22222,22 |

Subtotal . . . . . . . . . . | 44444,44 | . . . . . 44444,44 . . . . . . . | . . . . . . . . . . . . . . .

And on this Subtotals I may have positive and negative values, right? I wish to sum positives and store in one variable, and sum the negatives and store in another variable. Those variables I have here I need to Sum (Because I will assign a new field, out of sections).

For the case above the amount of my variable should be:

SumofPositives: 66666,66

SumofNegatives: -44444,44

ok?

But here I can not progress, because due to the inconstancy I am facing on Webi the sum is returning inconsistent value.. It returns for example:

SumofPositives: 180000,99

SumofNegatives: -409834233,43

Due to this situation I need to change the way to works.

[Former Member · Accepted solution]

It sounds like your "amount" field is actually a character field. That would explain why the sum button is disabled. Also, is the amount a measure or a dimension? If you sum on a dimension, I believe the default behavior is to do a sum of distinct values only, meaning any duplicate values will be ignored and your sum will be wrong.

[Former Member · Accepted solution]

Hi Dave!

All the Variables I created as Measures. Is there any other thing I have to check?

[Former Member · Accepted solution]

Are they numeric? What is your data source, is it a relational database via a universe, a bex query, or something else? What are the source object types? Meaning, it's not important what your variable types are if the source types are still dimensions...

[Former Member · Accepted solution]

Dave, my provider is a bex query, on the query I have 0AMOUNT.

[Former Member · Accepted solution]

Is your block a vertical table? And if you right click in the amount column, do you have the option to 'Format Number'?

If yes, then I'm surprised that the sum button is not activated. Try creating a new tab, inserting just profit centre and amount, is the sum button then lit?

In your universe, this measure might be set to an aggregation of 'Database Delegated'. If it should just add, this is unnecessary, so try changing this to 'Sum'.

With this as sum, and in a brand new block, I'm guessing you'll have no problems.

[Former Member · Accepted solution]

Yes David, thats right. I have a vertical Table and I can do a format number and i cannot Sum.

If I want to sum the "movement" data, I mean line by line for each profit center I have no problems, but if I try to sum the sum, i Cannot.

On universe is already setted as "Sum" instead "Database Delegated".

Still having problem

[Former Member · Accepted solution]

OK, rather than using an IF statement, try using 'Where'. Something like:

=Sum(Main.MontantePerc.Montante where(Main.MontantePerc.Montante > 0))

[Former Member · Accepted solution]

It didn't work. I asslembled it again and again and no way.

[Former Member · Accepted solution]

Hi Flavia,

Can you check the following property in the universe designer.

Go to the measure, double click and check in the properties if the measure property is set to "database delegated". If yes, then you will not be able to do any calculation on it.

Change it to Sum and then try out. It should work.

Hope this helps you.

Regards,

Bhargava Bommidi.

[Former Member · Accepted solution]

Hi Bhargava,

I tried this already and didn't work!

Thanks the reply.

[Former Member · Accepted solution]

Hello everyone,

I am closing this topic, the problem was solved using context operators.

tks efforts.