Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggr and Setanalysis

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

5 Replies
Not applicable
Author

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?

Not applicable
Author

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

Not applicable
Author

Can anyone Help??

Thanks

Sravan

Not applicable
Author

[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

Not applicable
Author

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