topic Re: Aggr function and set analysis in QlikView

Aggr function and set analysis

chriswain2 — Mon, 16 May 2016 09:55:24 GMT

I would normally be able to solve this by doing the work in the script, but I have to use the aggr method because I am using an input field which is not there in the script. It works except for one thing: I want it to evaluate the max date expression highlighted based on the current part number it has reached in the aggr loop, but it is evaluating it based on all possible part numbers. I always have one part number selected in the model. The principle I need is how to use the value it has reached in the aggr loop. Is that possible?

sum({$<Part=,[Part Number]=,Planner=, [Work Station]= P([Work Station])>}

aggr( sum({$<Part=,[Part Number]=,Planner=, [Work Station]= P([Work Station])>-<Part=,Planner=, [Work Station]= P([Work Station]),[Part Number]={$(vPartsWithManualAdjustments)},

[Demand / Supply Date]={"<=$(=max({$<Part=,[Part Number]=,Planner=, [Work Station]= P([Work Station]) ,[Manual Supply]-={0}>} [Demand / Supply Date]))"}>}

aggr(

(sum({$<Part=,[Part Number]=,Planner=, [Work Station]= P([Work Station])>}[Suggested Quantity]*[Cycle Time In Seconds]/Cavities/3600/$(vEfficiency)*[Calculate Hours Flag])

-sum({$<Part=,[Part Number]=,Planner=, [Work Station]= P([Work Station])>}[Confirmed Supply Quantity]*[Cycle Time In Seconds]/Cavities/3600/$(vEfficiency)*[Calculate Hours Flag])

+sum({$<Part=,[Part Number]=,Planner=, [Work Station]= P([Work Station])>}[Demand Quantity]*[Cycle Time In Seconds]/Cavities/3600/$(vEfficiency)*[Calculate Hours Flag])

)

,[Demand / Supply Date])),[Demand / Supply Date],[Part Number]))

Re: Aggr function and set analysis

sunny_talwar — Mon, 16 May 2016 10:11:37 GMT

I think what you might be looking for is the FirstSortedValue() function. I am not sure I understand your current expression well enough to offer any changes. but the idea is something like this:

FirstSortedValue(FieldName, -Date)

Check more about FirstSortedValue here: Value Associated with Min/Max Value of Another Field (Front End Solution)

Re: Aggr function and set analysis

chriswain2 — Mon, 16 May 2016 10:15:20 GMT

Thanks, Sunny. I will try it and post the solution if I get it working.

Re: Aggr function and set analysis

chriswain2 — Mon, 16 May 2016 10:43:03 GMT

I could not get that to work unfortunately. If I take out the max date expression and put it in a straight table with TOTAL <[Part Number]> added to it as below, it gives the result I want, but in the main expression it has no effect.

date(max({$<Part=,[Part Number]=, Planner=,[Work Station]=p([Work Station]) ,[Manual Supply]-={0},[Demand / Supply Date]=>}TOTAL <[Part Number]> [Demand / Supply Date]))

Re: Aggr function and set analysis

sunny_talwar — Mon, 16 May 2016 17:40:26 GMT

Is there any way you can share a sample?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: Aggr function and set analysis

chriswain2 — Tue, 17 May 2016 07:51:52 GMT

Hi Sunny,

I had a look at doing that but because of the fact that the data from the inputfields would disappear it would be very difficult for anyone to see what I would be trying to achieve. I have been asked to move on to other tasks as this was a nice to have rather than an essential requirement, but thanks for your help.

Chris