Sum expressions with different dimension field but having same dimension value

[#1]

Hello all,

I have a problem with resolving the following situation. I have multiple geographic area's that are copied in multiple fields. Let's simplify the problem to two fields:

COMPONENT_RESPONSIBLE

DESIGN_RESPONSIBLE

So the content of both thoose fields is the same = [Area1, Area2, Area3, ..]

Let's say I have 2 expressions (in reality they are little bit more complex):

sum(DEFECTS_COMPONENTS)

sum(DEFECTS_DESIGN)

I can perfectely make a chart that gives me an overview for 1 expression. But I cannot combine both expressions just like that in one chart because the dimensions differ that I have to use for both expressions. What I want to achieve is a chart that gives me:

Dimension	Defects
Area 1	sum({<COMPONENT_RESPONSIBLE={'Area 1'}>}DEFECTS_COMPONENTS) + sum({<DESIGN_RESPONSIBLE={'Area 1'}>}DEFECTS_DESIGN)
Area 2	sum({<COMPONENT_RESPONSIBLE={'Area 2'}>}DEFECTS_COMPONENTS) + sum({<DESIGN_RESPONSIBLE={'Area 2'}>}DEFECTS_DESIGN)
Area 3	sum({<COMPONENT_RESPONSIBLE={'Area 3'}>}DEFECTS_COMPONENTS) + sum({<DESIGN_RESPONSIBLE={'Area 3'}>}DEFECTS_DESIGN)
...

I could do this with a data island as dimension and then using a nested if expression and hard coded 'Area x' values in the set analysisses but I've got alot of those area's to cover (not to mention expressions...)

Does anyone has an idea how to do this better? Thanks!

[#2]

I'm having this exact problem, and am about to go the Data Island route. Does anyone know of a better way to solve this using Set Analysis?

[#3]

Hi both

In a perfect world you would change the database structure so that the area is somehow in a shared field, eg in a table with the fields

Responsible ("component or design")

Area(area1/2/3/....x)

Defects

I'm guessing this is already not possible, so here is my suggestion, without changing the script.

You can use valuelist() or valueloop() to generate dimension values in a chart, which you can then reference in the expression.

So your table would look something like this:

Dimension: valueloop(1,10,1)  (genereates 10 values, 1 to 10)

Expression: sum(if(component_responsible = 'area' & valuelist(1,10,1),defects_components))

+sum(if(design_responsible = 'area'&valuelist(1,10,1),defects_design))

In the first sum() I look at all the data and compare each value of component_responsible to the word "area" followed by the number generated in the dimension.

Then I do something similar for the design. Above is assuming you have 10 areas, but you can insert a variable into the function that returns the total number of areas.

Hope this helps.

On a similar thread (and again much easier) you could create a stand alone table of distinct areas.

EG.

Area_table:

load distinct Component_responsible as Area resident main_table;

You could then use that as the dimension and perform a similar expression to the one above where you compare your component responsible and design responsible to the stand alone area dimension.

Erica