Dynamic Targets in Each KPI selection.

[#1]

Hi Guys,

I am in desperate need of help. I have attached a qvw file here that shows you Sales and Cost data where my dimensions are 'Area' 'Region' 'YearWeek'.

I have two challenges which I hope to receive some advice:

For the CHART OBJECT, how can I write the expression so that if I choose an expression from the Cyclic button say Cost% or Margin, a corresponding Target  will be loaded from a Target table. Right now, I just placed '15%' as a static target. Is there anyway I can create a dynamic Target expression that changes as you choose Area or Region?

For the Scorecard Object, please refer to the Visual Cue's for Cost% part. I have written an expression so that the results will get color coded based on the corresponding targets or threshold which are in the 'Targets' table. However, it seems the targets are not being loaded at all. Since the Scorecard has a drill down capability, if I choose a REGION, it should automatically load an AREA target because it is a drill down. If i don't have any selections for Area or Region, it should show Region Targets. I was thinking if this kind of approach can be applied to the color coding in the visual cue tab.

Please help, I have been working 3 days on this and I just can't pin it down, I would really appreciate your support.

Is there something wrong in the format of my target table? is there any re arrangement I can do with my targets table, since targets for AREAS are different from REGION targets, I couldn't do 'aggregation' If i could only aggregate the targets then this would have been an easy task as it follows how Qlikview work. Please help.

I have attached both QVW and Excel sample file.

Cheers,

Jereme

[#2]

I think you need to make some choices.

1) An option would be to use only region targets and assign a region to each area.

2) A second option would be the inverse, use only area targets and assign a region to each area.

1 and 2) Either way, you appear to need to have a value for both region and area in each of your rows.

3) Another option would be to combine the area and region columns so that there is a single column that contains both the area values and the region values. In qlikview, you would then have a single list box to select areas or regions with their associated targets.

Your data structure does not appear to be functional in the excel file as provided.