topic Aggr and Setanalysis in QlikView

Aggr and Setanalysis

Wed, 16 Mar 2011 10:14:45 GMT

Hi Guys,

After struggling for sometime and could not found the way to write the Expression with setanalysis and aggr function, I am posting this here in Forum. May be someone can help me with the Expression described in the Excel sheet attched

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.00.49.52/Problem-with-aggr.xls]

Regards

Sravan

Aggr and Setanalysis

Wed, 16 Mar 2011 14:07:13 GMT

This one is going to be difficult to troubleshoot with just that Excel sheet. Complicated Set Analysis/Aggr functions are very dependent on the data structure, so troubleshooting expressions without sample data is difficult.

Looking at what you have, I think you have too many Aggr functions. All of your Aggrs are on the same field MachineDefinitionId. You should be able to get rid of at least one.

I would try:

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

There are also two fields which are not within aggregate functions. Since Aggr is something like a Group By in SQL, I think you may need all other fields to be within aggregate functions. If there is only one value per MachineDefinitionId, you could use Only (otherwise, Max may be the best choice.

Like:

=sum(
 aggr(
 (
 (Only([set (pcs/sec)]) *
 sum({<ProductCategory ={'G'} >} ActivityDuration)) -
 sum(Output)
 ) * Only(StandardValueAddedTime) ,
 MachineDefinitionId)
)

Those are just some ideas based on your sample. What is your current expression returning? All Nulls?

Aggr and Setanalysis

Wed, 16 Mar 2011 15:34:25 GMT

Hi Miller,

Thanks for your response. Yes I get '0' as answer, which is wrong. The main Problem lies on the following fieldvalues. If they are all same the answer is correct as in case1

StandardValueAddedTime, SetValueAddedTime, Std(pcs/sec) and set(pcs/sec)

In the first case they are same and in the second case, they are not. In firstcase, I can aggregate with the ProductCatgory= G as all the above fieldvalues are same. Hence the formula works.

In the second case expression, I have to aggregate with two fields according to my understanding. These are by ActivityCategory= P and also CycleActivityId to get the "Group By" as you said. I dont know how to write this in Expression.

If you still need Eqample, I will make an example and attach for few shifts.

Thanks once again

Sravan

Edit: The Expressions you attached did not work

Aggr and Setanalysis

Thu, 17 Mar 2011 07:03:16 GMT

Can anyone Help??

Thanks

Sravan

Aggr and Setanalysis

Sat, 19 Mar 2011 13:52:13 GMT

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.00.49.52/ForumproblemCalc.qvw]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.00.49.52/QlikViewConnectorForum.xlsx]

Hi Guys,

I made an example in Qlikview and also attached is the excelsheet, where the correct figures are present.

For shifts 20, 3173, 3176 the Minor stops and Performance calculation based on Minorstops is wrong. I need correct values for these to be equal to the excelsheet attached.

I need a common formula which satisfies all the shifts.

Hope someone has an Idea.

Thanks

Sravan

Aggr and Setanalysis

Mon, 21 Mar 2011 17:14:38 GMT

I think I am dealing with something two difficult or too easy that I did not get answer. Hope somebody who is good in Setanalysis helps..

Thanks

Sravan