
Aggr and Setanalysis
Neil Miller Mar 16, 2011 10:07 AM (in response to Sravan Puppala )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
Sravan Puppala Mar 16, 2011 11:34 AM (in response to Neil Miller)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
Sravan Puppala Mar 17, 2011 3:03 AM (in response to Sravan Puppala )Can anyone Help??
Thanks
Sravan

Aggr and Setanalysis
Sravan Puppala Mar 19, 2011 9:52 AM (in response to Sravan Puppala )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
Sravan Puppala Mar 21, 2011 1:14 PM (in response to Sravan Puppala )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



