topic Re: Set Analysis in QlikView

Set Analysis

Wed, 25 Nov 2020 16:16:04 GMT

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??

Re: Set Analysis

marcus_sommer — Fri, 08 Jul 2016 13:39:46 GMT

Try:

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

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

- Marcus

Re: Set Analysis

mgranillo — Fri, 08 Jul 2016 13:45:24 GMT

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

Re: Set Analysis

Fri, 08 Jul 2016 13:48:50 GMT

thank you but it did not work

Re: Set Analysis

Fri, 08 Jul 2016 13:59:13 GMT

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

Re: Set Analysis

marcus_sommer — Fri, 08 Jul 2016 13:59:27 GMT

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

Re: Set Analysis

Fri, 08 Jul 2016 14:03:42 GMT

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)

Re: Set Analysis

marcus_sommer — Fri, 08 Jul 2016 14:09:45 GMT

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