topic Re: TOTAL calculation in QlikView

TOTAL calculation

nihhalmca — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am doing TOTAL with one measure and two dimension for instance:

Month, Product, Revenue

Jan, A, 1000

Feb, B, 4000

---

I want to display this data in line chart, can you please help with expression please.

I tried below expression but it not giving right values.

1. Sum(Revenue)/Sum(Total Revenue)

2. Sum(Revenue)/Sum(Total <Product> Revenue)

3. Sum(Revenue)/Sum(Total <Month> Revenue)

4. Sum(Revenue)/Sum(Total <Month,Product> Revenue)

Thanks,

Nihhal.

Re: TOTAL calculation

MK_QSL — Thu, 06 Apr 2017 10:58:01 GMT

Can you provide the output you are looking along with sample data?

Re: TOTAL calculation

swuehl — Thu, 06 Apr 2017 11:00:51 GMT

What is the correct or right value you want to see, in a line chart using which field(s) as dimension?

You need to describe your requirement if you want us to help you.

Re: TOTAL calculation

nihhalmca — Thu, 06 Apr 2017 11:34:49 GMT

Dimensions are Month and Company.

Measure is Revenue

If i take single dimension it works perfectly, i am getting issue when i tried to add second dimension in chart (Line).

Re: TOTAL calculation

MK_QSL — Thu, 06 Apr 2017 11:37:46 GMT

Again half information..

You want Month and Company as dimensions and

% As Revenue for each line?

Re: TOTAL calculation

MK_QSL — Thu, 06 Apr 2017 11:39:37 GMT

You can use

Month and Company as Dimensions

and

SUM(Revenue) / SUM(Total <Month> Revenue)

as expression

Re: TOTAL calculation

nihhalmca — Thu, 06 Apr 2017 12:43:10 GMT

Yes Manish.

Re: TOTAL calculation

nihhalmca — Thu, 06 Apr 2017 13:12:28 GMT

Hi Manish,

I used this logic earlier (Please see at expression in original post)

However my requirement is little bit different.

For instance:

Show data by your logic but whenever i select product=A in filter (listbox), chart shows expression value wrong (it calculate like 1000/1000).

But i want to calculate like 1000/5000 (as per my example in original post)

Re: TOTAL calculation

swuehl — Thu, 06 Apr 2017 13:48:07 GMT

Thanks for finally mentioning your requirement.

If you need to ignore user selections, you need to use Set Analysis.

Something like

=SUM(Revenue) / SUM({1} Total <Month> Revenue)

Re: TOTAL calculation

nihhalmca — Thu, 06 Apr 2017 14:30:25 GMT

Thanks Stefan.

Your expression is working with small change as

sum(Revenue)/sum({<Product=>} total <Month> Revenue)