Adding Goal Lines

[#1]

Hello all!

I have attached my file.  I have made a scrap chart and I am trying to add a goal line to it. 

The hierarchy of my data is as follows: Goal Group --> Goal Type --> Area --> Facility --> Department

So for example, on line 218 below, Department 002 is in the Electrical Facility in the Assy Area with Scrap as the Goal Type and Area as the goal group. (The Goal Group's Area and the Field name Area are unrelated - confusing since they are named the same - see picture below)

Whereas on line 198 below, Department 102 is in the 560XLS Facility in the Assy Area with Scrap as the Goal Type and Area as the goal group.

Both rows have starting goals of 2220716 because that starting goal number is the goal for the Assy Area.  For facility, the 560XLS goal and Electrical goal should add up to get 2220716.  I won't look at goals for the department level.

Does anyone know how to do this?  I can't get it to work.  The granularity may be off and I'm not sure how to fix it.

[Oleg_Troyansky]

It looks like the granularity issue - your goals are listed by 6 keys, while they are actually determined only by four. So, one solution could be using nested aggregation - something like this:

sum(

    AGGR(

              max(Goal)

              , YearMonthKey, GoalGroup, GoalType, Area)

)

Another problem that I spotted is that your actual Scrap levels are only defined per Department, not per the same combination of 4-6 keys, so any selections of Area or GoalType would throw off the comparison.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[Qrishna]

i See that concatenation in the Link table script is not proper.

That Might be affecting your front end analysis.

Would you mind sharing some sample data related to your problem?

[#4]

what kind of sample data?

[Qrishna]

Whatever you've used to demonstrate in the picture.