Set Analysis

[#1]

Dear all I need help, I need to sum up three expressions in a set analysis that i am using in a bar chart here is the expression

AGGR(SUM({<SessionType -={'Alpha', 'Bravo'},Location -={'Birmingham', 'Leeds'}>} [Value1] + [Value2] + [Value3] ), Location)/SUM({<SessionType -={'Alpha', 'Bravo'}>}SessionLength)

am i doing sumthing wrong??

[marcus_sommer]

Try:

sum(AGGR(SUM({<SessionType -={'Alpha', 'Bravo'},Location -={'Birmingham', 'Leeds'}>} [Value1] + [Value2] + [Value3] ), Location)) /

SUM({<SessionType -={'Alpha', 'Bravo'}>}SessionLength)

- Marcus

[mgranillo]

You have a granularity mismatch due to your aggr function. 

try wrapping another aggregation function around your aggr.

You may not need the aggr function.  a total modify might be what you need. 

Something like this:

SUM({<SessionType -={'Alpha', 'Bravo'},Location -={'Birmingham', 'Leeds'}>} total <Location> [Value1] + [Value2] + [Value3] )/SUM({<SessionType -={'Alpha', 'Bravo'}>} total SessionLength)

if you provide sample data dimension and desired result, i could be more specific

[#4]

thank you but it did not work

[#5]

i think the problem is on granularity issues  as you said because value the is not aggregated as the other so it needs to be aggregated to the same level as the other two the data is in this form

SessitionType [Value1 = delay],  [Value3 = Turnaound], [Value2 = Timeoutbeforeend]

Charlie           10mins                    10min                          -

Charlie          -                              20mins                         -

Charlie          -                              2mins                         20mins

[marcus_sommer]

Which result will be returned and which is your expected result? How looked the object and the datamodel? This meant you need to provide more details about your issue.

- Marcus

[#7 · Accepted solution]

I did this and it seems to work hope it works well

(SUM({<SessionType -={'Alpha', 'Bravo'},Location -={'Birmingham', 'Leeds'}>} total <Location> [Value1] + [Value2]) + SUM([Value3]) )/SUM({<SessionType -={'Alpha', 'Bravo'}>} total SessionLength)

[marcus_sommer]

An expression like: [Value1] + [Value2] + [Value3] will return NULL if any of the fields is NULL or not numeric. To avoid these you could use: rangesum([Value1], [Value2], [Value3]) which treated each not numeric value with 0.

- Marcus