Dividing sums with set expression not working?

[#1]

I want to show how some number has developed,  in percent, since a period in the past but I absolutely cannot get it to work. I simplified it to the bare minimum as:

sum(Value)/sum({<YearMonth={"201101"}>}Value)

This just won't work and gives me a chart with a single data point for 201101. I have attached a simple example. In reality there are more than one dimensions and 201101 is a variable, but even in the example it doesn't work as one would expect.

Any help would be greatly appreciated.

[swuehl]

Well,

sum({<YearMonth={"201101"}>} Value) is zero for all dimension values except 201101 (you also see this in the sum as drop in the line, only sum(Value) is displayed for those dimension values.

A division by zero is an invalid expression, thus it will not display.

[swuehl]

And to work around this:

sum(Value)/sum(total {<YearMonth={"201101"}>}Value)

[#4]

Thank you very much for your quick answer - you're right, it works. So, when going over the dimension values, the set expression doesn't override that "implicit" selection?

By the way, I was under the impression that doing

sum({1<YearMonth={"201101"}>}Value)

was the same thing as doing

sum(total {<YearMonth={"201101"}>}Value)

This is obviously not the case though since only the version with the total gives me the result I'm after.

[#5]

Also, if I have a second dimension (Product), how would that work? I couldn't use total then since it'd not use the Product in the dimension.

[swuehl]

An objects dimension is more like a group by, it's not exactely like a selection.

You could maybe look at your set expression sum({<YearMonth={201101}>} Value) like if you do an explicite selection by hand on that value (by clicking on 201101 in list box) while using sum(Value) as expression.

Create a straight table chart with dimension YearMonth, check both supress when value is null and show all values in dimension tab. uncheck supress zero values in presentation tab and use sum(Value) as expression. You will see, if you select 201101, that all values except 201101 are zero. That's because all Values which belong to other Months than 201101 are not within your current selection. Kind of logical, isn't it?

So, what sum(total {<YearMonth={201101}>} Value) does is that it disregards the dimensions, calculating the total sum over all YearMonth (there is only a result for 201101, so the sum over all YearMonth is equal to the result for 201101, right?).

If you use the set identifier 1 (like in sum({1<YearMonth= ....) you still regard the chart dimensions, so in your example, there is no difference (set identifier 1 says, disregard/clear all selections, not to disregard the dimensions!), if you don't have any other selections on other fields than YearMonth.

Hope this made somethings more clear.

And if you have more than one dimension? Well, I admit I haven't fully understood your last sentence, but total may take a list of fields to again take into account, like

sum(total<Product>  {<YearMonth={201101}>} Value)

will disregard YearMonth dimension, but still regards Product dimension. Maybe this is what you need, then.

Regards,

Stefan