topic Re: Total Percentages in QlikView

Total Percentages

Anonymous — Wed, 07 Oct 2015 18:06:47 GMT

Hello my fellow QlikView friends. I am trying to understand what QlikView does when it totals percentages. As an example, see the example below. I have a table that looks like this:

Count % (Daily)

0.0%

-6.5%

0.0%

0.7%

0.5%

-1.0%

-0.8%

-0.6%

-3.0%

-2.4%

-0.2%

-1.1%

-1.9%

36.5%

0.0%

which QlikView totals as 0.0%

so what exactly is QLikView doing to get that total value of 0.0%? Thanks

Re: Total Percentages

sunny_talwar — Wed, 07 Oct 2015 18:08:25 GMT

It would depend on the expression you are using. Can you share your expression?

Re: Total Percentages

MK_QSL — Wed, 07 Oct 2015 18:14:14 GMT

In actual, it could be 0.000005% also and because you are showing only one decimal it is shown as 0.0%

Re: Total Percentages

hic — Wed, 07 Oct 2015 18:23:59 GMT

By default, QlikView does not sum the values of the lines. And it shouldn't either.

Instead, it calculates the expression over the entire data set. This makes a great difference when calculating ratios, as you do here. Let's say that you want to calculate

Sum( A ) / Sum( B )

and format this as a percentage. Then the most logical thing to do on the "Totals" line, is to calculate the same ratio but for the entire scope. Summing a number of percentages usually does not make sense.

That the total here is 0.0% does not need to strange. It could be that it is 0% for almost all data, and the numbers, e.g. 36.5%, could represent a very small fraction of the entire scope.

HIC

Re: Total Percentages

Anonymous — Wed, 07 Oct 2015 18:36:56 GMT

Thank you for the reply. My expression is basically

(COUNT(Today.IDNumber)-COUNT(Yesterday.IDNumber))/COUNT(Yesterday.IDNumber)

Re: Total Percentages

Anonymous — Wed, 07 Oct 2015 18:37:51 GMT

Thank you Henric. That makes sense

Re: Total Percentages

Anonymous — Wed, 07 Oct 2015 18:39:10 GMT

I am trying to replicate this in Excel. Any thoughts? Is it even possible in Excel?

Re: Total Percentages

Anonymous — Wed, 07 Oct 2015 18:44:25 GMT

Count (Daily) = Today (Count) - Yesterday (Count)

Count % (Daily) = Today (Count) - Yesterday (Count) / Yesterday (Count)

The 0.0% is what I am trying to replicate in Excel

Re: Total Percentages

Anonymous — Wed, 07 Oct 2015 19:13:14 GMT

Here are the numbers behind the percentages

Re: Total Percentages

Anonymous — Wed, 07 Oct 2015 19:19:56 GMT

After reading and re-reading your comments, I decided to apply the same formula

Count % (Daily) = Today (Count) - Yesterday (Count) / Yesterday (Count)

to the totals row instead of simply summing, as you suggested. It works. Thank you