topic Product of all Percentage of all rows in a table-diagram in QlikView

Product of all Percentage of all rows in a table-diagram

Tue, 18 Oct 2011 12:23:16 GMT

Hi,

I have a nice problem:

There is a table (diagram) containing a number of rows, a dimension and a percentage:

Account	Percentage
A	50%
B	30%
C	60%

What I want to see, is the product of all percentages (50% x 30% x 60% -> 0.5 x 0.3 x 0.6 -> 0.09 -> 9%) as a column:

Account	Percentage
A	9%
B	9%
C	9%

How can I achieve this?

Thanks a lot in advance,

Martin

Product of all Percentage of all rows in a table-diagram

swuehl — Tue, 18 Oct 2011 12:52:21 GMT

You could try something like

=exp(sum(total log(Percentage)))

as expression in your chart.

And maybe you want to vote for http://community.qlik.com/ideas/1105

Regards,

Stefan

Product of all Percentage of all rows in a table-diagram

Tue, 18 Oct 2011 13:08:44 GMT

Hi Stefan,

thanks, but this only gives me 100% for all rows...

And i voted.

Martin

Re: Product of all Percentage of all rows in a table-diagram

swuehl — Tue, 18 Oct 2011 13:11:55 GMT

See attached for a sample.

How do you calculate your percentage?

Stefan

Re: Product of all Percentage of all rows in a table-diagram

Tue, 18 Oct 2011 13:12:52 GMT

If I would get the product of all percentages as a total row it would also be ok:

Account	Percentage
A	50%
B	30%
C	60%
Total	9%

Thanks,

Martin

Re: Product of all Percentage of all rows in a table-diagram

Tue, 18 Oct 2011 13:27:32 GMT

Hi,

my percentage is something like "sum(CounterM) / sum(VK_MENGE)"

Thanks,

Martin

Product of all Percentage of all rows in a table-diagram

swuehl — Tue, 18 Oct 2011 13:31:16 GMT

Then try something like

exp(sum(total aggr(log(sum(CounterM) / sum(VK_MENGE)),Account)))

Re: Product of all Percentage of all rows in a table-diagram

Tue, 18 Oct 2011 13:44:23 GMT

Hmm... like that I get only 0% everywhere... and when I use another expression with "sum(CounterM) / sum(VK_MENGE)" as xxx and use the result of this in the main formula "exp(sum(total aggr(log(xxx),Account)))", I get a 100% everywhere... can you see my mistake?

Re: Product of all Percentage of all rows in a table-diagram

swuehl — Tue, 18 Oct 2011 13:44:40 GMT

Some more examples.

Stefan

Re: Product of all Percentage of all rows in a table-diagram

swuehl — Tue, 18 Oct 2011 13:51:31 GMT

Could you upload a simplified version of your app here in the forum? Or just some LOAD INLINE tables as text?

Re: Product of all Percentage of all rows in a table-diagram

Tue, 18 Oct 2011 14:34:32 GMT

Here we go. 🙂

Re: Product of all Percentage of all rows in a table-diagram

swuehl — Tue, 18 Oct 2011 14:54:45 GMT

I think something like

exp(sum(total aggr( log( Expression3/Expression1),TransID)))

in fact does work also in your example. It's just that your numbers are getting pretty small (note that we use a Product function here...).

You also have sometime multiple records per TransID, so you might want to use an aggregation on the percentage, maybe

exp(sum(total aggr( log( sum(Expression3)/sum(Expression1)),TransID)))

exp(sum(total aggr( log( sum(Expression3/Expression1)),TransID)))

depending what you want to achieve.

edit: see attached your data with more decimal places added