topic Re: Divided by dimension total in QlikView

Divided by dimension total

Tue, 26 Aug 2014 14:04:04 GMT

Hi,

I am trying to figure something out.

i have a sales case in which i sell cars. i have VW, BMW and Audi's.

every week i get a report on the sales from this week. i can see which cars are selling the best each week.

i can also see the contribution of each car in %

for example

week 20

Brand	Amount	Contribution
Audi	12	18%
BMW	20	30%
VW	35	52%
Total	67	100%

The above table is easy to replicate in QV.

But now i want to see all weeks in the same table but i want to see the contribution of a Brand for each week and not the contribution on the year total.

Week	Brand	Amount	Contribution
20	Audi	12	18%
20	BMW	20	30%
20	VW	35	52%
21	Audi	20	20%
21	BMW	35	35%
21	VW	45	45%

is it possible to create it like that?

Re: Divided by dimension total

simondachstr — Tue, 26 Aug 2014 14:08:30 GMT

You need to create Week-field, ideally by using the following expression: week('Date') as Week.

Re: Divided by dimension total

Tue, 26 Aug 2014 14:12:10 GMT

I have a master calendar which contains a week-field. that is not my question.

I want to know if it is possible to replicate my table above.

The % is calculated in the table and not a value in my datasource

Re: Divided by dimension total

Anonymous — Tue, 26 Aug 2014 14:20:24 GMT

try in the table and show in percentage:

contribution: sum(Amount)/sum(total <Week> Amount)

Re: Divided by dimension total

Anonymous — Tue, 26 Aug 2014 14:22:03 GMT

Nard

You'll need to use the total qualifier in your expression. The Chart Aggregation functions section of the Help describes it at length.

Re: Divided by dimension total

alematex — Tue, 26 Aug 2014 14:28:54 GMT

Hi:

This is possible using your week as a calculated dimension.

Re: Divided by dimension total

simenkg — Tue, 26 Aug 2014 14:32:37 GMT

How about something like this:

avg(aggr(nodistinct sum(Amount)/sum(Total <Week> Amount),Week,Brand))

Re: Divided by dimension total

jaimeaguilar — Tue, 26 Aug 2014 14:35:43 GMT

Hi,

you can create a straight table with week (or weekyear) and brand as dimensions. Also you can have the same amount expression as in your example. What will change is that your expression for the weekly contribution per brand has to be something like this:

sum(Amount) / sum(total <week> Amount)

of course, you may add any additional filter (with set analysis or direct selections) so you can get to the result you need,

hope this helps,

regards

Re: Divided by dimension total

MarcoWedel — Tue, 26 Aug 2014 14:44:52 GMT

Hi,

one example:

hope this helps

regards

Marco

Re: Divided by dimension total

Tue, 26 Aug 2014 14:50:21 GMT

Yes this works perfect, thank you