topic How to apply Excel Formula in QV in QlikView

How to apply Excel Formula in QV

muniyandi — Wed, 12 Mar 2014 11:10:31 GMT

Hi,

I need excel formula % of Row Total, % of Column Total details and how to apply in qlikview. any sample report are available it will be useful.

1. % of Row Total

2. % of Column Total

With Regards,

Muni.

Re: How to apply Excel Formula in QV

amit_saini — Wed, 12 Mar 2014 11:17:23 GMT

Hi Mathew,

You can call your expression in any chart and select Expression total option . Please see below:

For percentage purpose you can select option here under Number of chart properties :

Thanks,

Re: How to apply Excel Formula in QV

MK_QSL — Wed, 12 Mar 2014 11:24:14 GMT

Like This ?

Re: How to apply Excel Formula in QV

muniyandi — Wed, 12 Mar 2014 11:56:38 GMT

Thanks for quick reply,

i want the result is all row value accumulated means get value 100%, all Column value accumulated means get value 100%

Thanks,

Muni

Re: How to apply Excel Formula in QV

MK_QSL — Wed, 12 Mar 2014 12:00:55 GMT

I have given three result

1) Customer wise Sale

2) Customer wise Sale Compare to TOTAL Sale

3) Customer wise Sale Compare to TOTAL Sale but with respect to YEAR

Re: Re: How to apply Excel Formula in QV

stevedark — Wed, 12 Mar 2014 12:08:52 GMT

You need to divide the sum for a dimension by the sum of the whole chart, this is done like this:

SUM(Sales) / SUM(TOTAL Sales)

I've used Manish's QVW as a basis for showing this...

If you want to work out the percentage of a row you need to divide by the total for the row, which is this:

SUM(Sales) / SUM(TOTAL <Customer, Country>Sales)

And then for each column it is like this:

SUM(Sales) / SUM(TOTAL <Year>Sales)

Amended QVW attached.

Hope that helps.

Steve

Re: Re: Re: How to apply Excel Formula in QV

muniyandi — Wed, 12 Mar 2014 12:46:42 GMT

Hi Steve,

Thanks for reply, % of column total is working well, i want more information on % of row total formula
Kindly refer my attachment file.

Thanks,

Muni.

Re: How to apply Excel Formula in QV

MK_QSL — Wed, 12 Mar 2014 12:52:28 GMT

Have you check my reply?

Re: Re: Re: How to apply Excel Formula in QV

stevedark — Wed, 12 Mar 2014 13:01:25 GMT

It's the TOTAL keyword that is important. The example I uploaded gave examples of this for % of entire sum as well as column and row.

To apply the same to your example you need to change your expressions to:

sum(avalue)/(sum(TOTAL avalue)+sum(TOTAL bvalue))

and

sum(bvalue)/(sum(TOTAL avalue)+sum(TOTAL bvalue))

Steve

Re: Re: Re: How to apply Excel Formula in QV

muniyandi — Wed, 12 Mar 2014 13:03:45 GMT

Hi Manish,

you are achieve % of Column Total, one more think i need % of Row Total , how to add the expression dynamically. can you check last my reply attachment file in % of row total chart.

Thanks,

Muni.

Re: How to apply Excel Formula in QV

MK_QSL — Wed, 12 Mar 2014 13:07:17 GMT

Use below as an expression in my file..

RangeSum(Above(TOTAL SUM(Sales),0,RowNo(TOTAL)))/SUM(TOTAL <Year> Sales)

Re: Re: Re: How to apply Excel Formula in QV

muniyandi — Wed, 12 Mar 2014 13:16:53 GMT

Thanks for All

Steve expression is correct.

sum(avalue)/(sum(TOTAL avalue)+sum(TOTAL bvalue))

and

sum(bvalue)/(sum(TOTAL avalue)+sum(TOTAL bvalue))

===============================================

any option to dynamically add to expression?

Ex:

i have condition to enable the expression.that time condition has passed then sum(total bvalue) expression will be adding.

Thanks,

Muni