Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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
Can anyone Help??
Thanks
Sravan
[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
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