topic SetAnalysis in QlikView

SetAnalysis

Wed, 01 Dec 2010 13:21:09 GMT

Hi Guys,

I wanted to group all the ActivityDefinitionId which are same(124) in the Below SetAnalysis Formula. How can do it?


([set (pcs/sec)]* ActivityDuration - sum({$<ActivityDefinitionId}>}Output))*StandardValueAddedTime

SetAnalysis

pover — Wed, 01 Dec 2010 22:49:35 GMT

Suming expression values with exist within the same dimension value is done by using Total. For example:

sum(Total <ActivityDefinitionId> Output)

Regards

SetAnalysis

Wed, 01 Dec 2010 23:15:07 GMT

Hello carl,

can you change my expression with Total? I am not getting the syntax right.

Thanks for ur answer

Sravan

SetAnalysis

pover — Wed, 01 Dec 2010 23:25:07 GMT

Does this work?

([set (pcs/sec)]* ActivityDuration - sum(Total <ActivityDefinitionId> Output))*StandardValueAddedTime

SetAnalysis

Mon, 06 Dec 2010 17:54:48 GMT

Hello Karl,

It works but one more extension


sum({<ProductCategory = {'G'}>}ActivityDuration*([set (pcs/sec)])-(sum(Total <ActivityDefinitionId> Output))*StandardValueAddedTime)/sum({<ProductCategory = {'G'}>} ActivityDuration)

some comma somewhere is wrong I guess or ')' can you help me?

Thanks in Advance

sravan

SetAnalysis

Mon, 06 Dec 2010 18:56:06 GMT

You are using Sums inside another Sum function. That does not work. If you need the nested Sums, you must use the Aggr() function.

When using the Aggr() function, you will need to define the dimensions you wish to aggregate by. Maybe something like this:

sum({<ProductCategory = {'G'}>} Aggr(
ActivityDuration*([set (pcs/sec)])-
(sum(Total <ActivityDefinitionId> Output))*
StandardValueAddedTime, ActivityDefinitionId))
/sum({<ProductCategory = {'G'}>} ActivityDuration)

But I think you will need to add each dimensions in your chart as parameters to the Aggr() function:

sum({<ProductCategory = {'G'}>} Aggr(
ActivityDuration*([set (pcs/sec)])-
(sum(Total <ActivityDefinitionId> Output))*
StandardValueAddedTime, ActivityDefinitionId, OtherDim, OtherDim2))
/sum({<ProductCategory = {'G'}>} ActivityDuration)