topic Excluding and including data for an aggregation in QlikView

Excluding and including data for an aggregation

Wed, 16 Feb 2011 13:16:21 GMT

Hey there,

I'm trying to work out a pretty easy formula for a pretty easy value:

I have a company dimension, which holds legal entities with their respective groups:

example: Group 1 belongs to 3 entities - the whole information is saved in one table.

Now my problem:

I want to build something like a gauge-based KPI - which shows the actual summarized year sales amount for the selected lega entity in relation to it's groups sales amount:

SUM(Selected Entity Sales Amount)/(SUM(Group Sales Amount of selected entity)/COUNT(entites of group)

Any ideas?

Excluding and including data for an aggregation

Miguel_Angel_Baeyens — Wed, 16 Feb 2011 13:45:44 GMT

Hello,

Have you tried using TOTAL <Field> ?

Sum(Amount) / Sum(TOTAL <EntityID> Amount)

Hope that helps.

Excluding and including data for an aggregation

Wed, 16 Feb 2011 14:06:35 GMT

I've tried TOTAL as well but it didn't turn out as what I wanted it to be 🙂

It's looks like TOTAL is also affected by the selection in the field list, as it allways shows me 100%.

But that's not what I want it to show.

I want it to compare the Sales Amount of the selected Entity with the avg sales amount for the whole group, where 100% is ok, 75% is bad and 100+% is good, by now the scale is all I could manage 😄

Is there a way to make objects inside a dashboard "independent", so they will only be effected by some, not all selections. In my case I want that all selections will be ignored except the company dimension.

Excluding and including data for an aggregation

Miguel_Angel_Baeyens — Wed, 16 Feb 2011 16:23:48 GMT


P.Schmerbach wrote:Is there a way to make objects inside a dashboard "independent", so they will only be effected by some, not all selections. In my case I want that all selections will be ignored except the company dimension.

There are ways to keep your values agains the whole data. If TOTAL gets all values in expression, set {1} means all values in dimension, so combined should return what you want regardless the selection:

Sum(Sales) / Sum({1} TOTAL Sales)

Hope that helps.

Excluding and including data for an aggregation

Wed, 16 Feb 2011 17:35:35 GMT

Your suggestion will give me the overall total sales summarized... but thanks for your help 🙂

By now I found a solution for my problem... it's really hard implementing some "easy" business logic steps into this nice point & click adventure 😄

My solution looks like this:

(((SUM({1<Client=P({$<Client>} )>} Umsatz)-Sum(Umsatz))<>0),Sum(Umsatz)/((SUM({1<Client=P({$<Client>} )>} Umsatz)-Sum(Umsatz))/COUNT(TOTAL CompanyKey

)), 0)