SetAnalysis+ Aggr

[#1]

Hello Guys,

I am struggling with this formula to get it correct in Qlikview as in Excelsheet, It involves Setanalysis and Aggr function. I would be thankful if anybody can help me.

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

From this sheet, I want the values in Red and Green to be calculated in Qlikview.

My try is in this .qvw

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

Attached Excelsheet and Qlikview application.

Thanks

Sravan

[#2]

Hi

At a quick look on it I see two problems. I would try to load in the data for product A and B separately as you have no way of distinguish them now.

Second, when you use aggr, remember to also put in your dimensions (in this case Prod A and B which you need to name as a column)

aggr(expression, dimension)

Then you can do the sum. I will have a closer look if I get the time later.

//John

[#3]

Hi John,

Thanks for your answer. This is a simple example.The SQL Database is having only one such table for both the Products A and B and the ActivityDefinitionID differentiates the products A and B.

There are hundreds of Products and hence I want to generalise the common equation based on this ActivityDefinitionID which differentiates the Products which is run on that Particular Date or shift.

For Aggr I used the Dimension, if you can see in formula as aggr(Expression, ActivitityDefinitionID)

Please check again if you have time.

Thankyou

Sravan

[#4]

No Ideas???

[#5]

I was unable to open your Excel file; I'm stuck with the old version. I did look at your QVW.

First, create a chart with your chart dimension, Shift, and with your Aggr dimension, ActivityDefinitionID. Then use your expressions with the Aggr taken out, so:

([set (pcs/sec)]* ActivityDuration - sum(Output))*StandardValueAddedTime

You can then add a Sum total to that expression to see what your Aggr function is doing. When I do that, I get 2664 for the total, all on TBNNAME88. From there, you should tweak your expression until you get your desired total. It helps to get your expression working before you add in the Aggr function. Since you're not getting the desired result, even before using Aggr, there is something wrong with your expression.

If you could respost the xlsx as an xls or csv, I can take another look.

[#6]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.00.49.52/ForumProblem-_2D00_-Kopie.xls]

Hi Miller,

Yes I am stuck in there and I am not getting the expression what I need. I want the sum of TBNNAME87 and TBNNAME88 together, which I am not able to get.

I am attaching the excel in Excel 97-2003 format so that you can open.

Hope you can Help me. I got all the other formulas correct and if I get this I am done.

Thanks again.

Sravan

[#7]

In your QlikView file, the ActivityDuration for TBNNAME87 is null. In your Excel file, it is 3600. If it was set to 3600, your expression should work as is. The expression is correct, the data is off.

[llauses243]

Hi ,

Before pls review image attach

This is my offer see .rar

Good luck, Luis

[#9]

Hi Guys,

Thanks for your help. I could now understand that for set analysis it must be done always stepwise to get to the desired total. I learned about aggr etc from this Question.

I could achieve the Result of Minor stops with your help. But when I dont select anything it gives a different value but when I select something it gives the correct value. Is this because of the AGGR function in the Minorstops Formula. Only that value is not correct.

See the attachment. May be anyone can throw some light on it how I can get the correct answers with out selections

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

Thanks

Sravan