Apply expression on lower hierarchy level

Wed, 25 Jan 2017 09:38:12 GMT

Hello,

I'm working on a sales application. The goal is to display the margin by saler versus the margin objective. The salers are linked to a store. The objectives are defined by store and applied to every saler of this store.

In my model I have 1 fact table

ID_STORE | ID_SALER | ID_INDICATOR | VALUE

Store1 | Saler1 | Margin | 12%

Store1 | Saler2 | Margin | 17%

Store1 | | Margin Objective | 16%

In my QlikView application I want to display a table with 1 hierarchy (Store=>Saler) and 2 expressions :

- Objective: Avg ({<ID_INDICATOR={"Margin Objective"}>} VALUE)

- Margin : Avg ({<ID_INDICATOR={"Margin"}>} VALUE)

When the hierarchy is at the store level, the objectives are well displayed but when the hierarchy is at the saler level, as the objective is defined at the store level, it is not displayed in my table. I Would like to see the store objective at the saler level.

Hierarchy | Objective | Margin

Saler1 | 16% | 12%

Saler2 | 16% | 17%

How can I achieve that?

I could apply the objectives at every saler in my model, but there are a lot of salers and a lot of indicators, so I'm looking for a différent solution.

Thanks for the help!

Re: Apply expression on lower hierarchy level

sunny_talwar — Wed, 25 Jan 2017 10:21:32 GMT

May be this:

Aggr(Avg(TOTAL <ID_STORE> {<ID_INDICATOR={"Margin Objective"}>} VALUE), ID_STORE, ID_SALER)

topic Re: Apply expression on lower hierarchy level in QlikView

Apply expression on lower hierarchy level

Re: Apply expression on lower hierarchy level