topic Creating a value to show the % participation in QlikView

Creating a value to show the % participation

camilo — Wed, 13 Jul 2011 14:11:50 GMT

Hi, I would like to create a field that shows every line of my balance as a sales percentage, for example:

Account Real % of Sales

Sales 200 100%

Costs -40 -20%

Total 60 30%

I´m trying to create an expression "% of Sales" like this:

=sum(Real)/(Sum( {$<[Account]={Sales}>} Real))

But I just get this:

Account Real % of Sales

Sales 200 100%

Costs -40

Total 60 30%

In the DB, both Sales and Costs are in the same column (Account).

Thanks for your help!!

Creating a value to show the % participation

swuehl — Wed, 13 Jul 2011 14:17:58 GMT

Hi Camilo,

first, I think it should be

=sum(Real)/(Sum( {$<[Account]={'Sales'}>} Real))

(literal within {} because your want to filter for specific value).

Regards,

Stefan

Creating a value to show the % participation

camilo — Wed, 13 Jul 2011 15:18:58 GMT

Hi Stefan,

I tryied using

=sum(Real)/(Sum( {$<[Account]={'Sales'}>} Real))

But I got the same results.

Thanks,

Camilo

Creating a value to show the % participation

swuehl — Wed, 13 Jul 2011 15:22:09 GMT

Hi Camilo,

it might be helpful if you could post an example qvw.

I was also wondering why you got the above total row. I would expect 160 as value for real.

Regards,

Stefan

Creating a value to show the % participation

swuehl — Wed, 13 Jul 2011 15:35:08 GMT

Ok, please try

=sum(Real)/(Sum( total {$<[Account]={'Sales'}>} Real))

this works for me.

Stefan

Creating a value to show the % participation

camilo — Wed, 13 Jul 2011 15:53:41 GMT

Great Swuehl, it worked perfectly!!!

Why adding total did the job??

Many thanks for your help!!!!!

Creating a value to show the % participation

swuehl — Wed, 13 Jul 2011 16:07:08 GMT

Well,

sum( [{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression )

sum returns the aggregated sum of expression or field iterated over the chart dimension(s).

So if want to disregard the chart dimensions, we need the total qualifier.

We then may use a set expression to select the values to sum. If we don't disregard the chart dimension, we got an empty value list to sum, because the chart dimension in row (i.e. Costs) and selection set expression (Account=Sales) have no values in common. In other word, you need to override the chart dimension with total qualifier, the set expression won't do that.

Hope this helps,

Stefan

Creating a value to show the % participation

camilo — Wed, 13 Jul 2011 19:35:02 GMT

Thanks swuehl, now I understand how this works.

Camilo

Creating a value to show the % participation

camilo — Mon, 29 Aug 2011 19:08:49 GMT

Hi,

I have another question related to this topic.

I added month columns to separate the data, but the % of sales participation divide the month values by the total sales ¿is there any way to divide by the corresponding month sales?

I have this table:

Ex1	$$	% of Sales
Sales	110	100%
Costs	80	73%
Total	30	27%

I use moth to separate the data on columns:

	JAN		FEB		TOTAL
EX2	$$	% of Sales	$$	% of Sales	$$	% of Sales
Sales	40	36%	70	64%	110	100%
Costs	15	14%	65	59%	80	73%
Total	25	23%	5	5%	30	27%

I would like to have this kind of table having a 100% on the sales field every month:

	JAN		FEB		TOTAL
EX3	$$	% of Sales	$$	% of Sales	$$	% of Sales
Sales	40	100%	70	100%	110	100%
Costs	15	38%	65	93%	80	73%
Total	25	63%	5	7%	30	27%

Thanks to any one who can help me with this

Camilo

Creating a value to show the % participation

swuehl — Mon, 29 Aug 2011 19:28:39 GMT

Hi camilo,

without trying myself, I would have a look at the total qualifier with dimensions added (see above or help file, total<fld,fld>), i.e. replace the total by total<Month>

Re: Creating a value to show the % participation

camilo — Mon, 29 Aug 2011 19:50:43 GMT

I´m using this, but the result is the same(got the same values):

=sum(Real)/(Sum(total<Month>{$<[Cuenta Resu]={'Sales'}>} Real))

Thanks

Creating a value to show the % participation

swuehl — Tue, 30 Aug 2011 00:02:53 GMT

Just to be sure, 'Month' is your month field name? Because every field else is named in spanish...

Could you post a small sample application here?

Re: Creating a value to show the % participation

camilo — Wed, 31 Aug 2011 13:56:40 GMT

You are right I translated some words trying to make it more understandable Month is my Month field name, the exact line right now is:

=sum(Real)/(Sum( total{$<[Cuenta Resu]={'Ventas'}>} Real))

This line gives me the Sales participation percentage of every line and works fine when I have the information agregated.

Now I would like to use the variable Month to separate de data on Months columns. I did this and it looks fine but a closer look you can see that the sales (and all the other fields separated by moths) are being divided by the total sales not just the month sales wich result on a smaller participation % than what should be.

I´m attaching a sample app, hope is clear enough

Thanks,

Camilo

Creating a value to show the % participation

swuehl — Wed, 31 Aug 2011 14:02:02 GMT

So this is not what you want to see?

Mes	1	1	2	2	3	3
Cuenta	Real	Sales %	Real	Sales %	Real	Sales %
Costo1	130	43,3%	110	27,5%	98	25,8%
Costo2	30	10,0%	60	15,0%	77	20,3%
Ventas	300	100,0%	400	100,0%	380	100,0%

Re: Creating a value to show the % participation

camilo — Wed, 31 Aug 2011 14:07:57 GMT

Yes that is what I want to see!

Creating a value to show the % participation

swuehl — Wed, 31 Aug 2011 14:11:57 GMT

Yes - this is not what you want or yes - it is?

This is my expression:

=sum(Real)/(Sum( total<Mes> {$<Cuenta={'Ventas'}>} Real))

Looks similar to what I suggested before, doesn't it

Re: Creating a value to show the % participation

camilo — Fri, 02 Sep 2011 12:17:07 GMT

Stefan,

How can I create that table???

Thanks,

Camilo

Re: Creating a value to show the % participation

camilo — Fri, 02 Sep 2011 12:20:12 GMT

Yes that is what I want !!

Sorry I didn´t see you had already answered, I try your solution but it doesn´t show any data...

Re: Creating a value to show the % participation

swuehl — Fri, 02 Sep 2011 12:26:31 GMT

Here you are, I justed added the <Mes> after total in % expression.

Creating a value to show the % participation

camilo — Fri, 02 Sep 2011 14:09:57 GMT

It works perfectly!! I had an error on a variable.

Thanks Stefan!

Camilo