topic Re: Total row Straight table Percentage% calculation in QlikView

Total row Straight table Percentage% calculation

Wed, 27 Jul 2016 18:26:14 GMT

Hello,

I have the below in my application, its a straight table and i am displaying the Top 10 values for Groups.

And the Total row should display the Top 10 Sum values for the YTD, PY YTD, Var and Var%.

I can use Sum of Rows for the first 3 columns, but not for the % column which will give me the wrong result. I tried using the expression total, but not right result. Any suggestion would be helpful.

Thanks.

Group	Sales YTD	PY YTD	Var	Var%
LLL	419,439,519	537,374,285	-117,934,766	-28.1%
HHH	744,339,937	858,384,914	-114,044,977	-15.3%
SSS	338,613,157	441,800,776	-103,187,619	-30.5%
AAA	241,717,161	344,476,279	-102,759,118	-42.5%
CCC	388,151,255	470,200,691	-82,049,436	-21.1%
HSHS	275,213,194	309,458,551	-34,245,357	-12.4%
VVV	81,173,492	113,489,048	-32,315,556	-39.8%
SYSYSY	56,938,900	83,504,770	-26,565,870	-46.7%
MOOR	229,326,613	252,359,634	-23,033,021	-10.0%
SSWRT	75,485,125	96,555,761	-21,070,636	-27.9%
Top 10 Total	2,850,398,352	3,507,604,710	-657,206,358

Re: Total row Straight table Percentage% calculation

antoniotiman — Wed, 27 Jul 2016 19:22:10 GMT

Try

If(Dimensionality() = 0,
(Sum(TOTAL SalesYTD)-Sum(TOTAL PYYTD))/(Sum(TOTAL SalesYTD)),
(Sum(SalesYTD)-Sum(PYYTD))/(Sum(SalesYTD)))

Regards,

Antonio

Re: Total row Straight table Percentage% calculation

sunny_talwar — Wed, 27 Jul 2016 19:32:28 GMT

Would you be able to provide raw data behind the above output. I feel that it would be easier to propose something knowing how the data looks.

Re: Total row Straight table Percentage% calculation

Thu, 28 Jul 2016 09:35:32 GMT

Thanks for the reply Antonio, however i could not relate your reply to my question.

Re: Total row Straight table Percentage% calculation

Thu, 28 Jul 2016 09:37:18 GMT

I might not be able to share the Raw data here, but can tell you that, it is daily transaction data and i am calculating YTD and PY YTD using set analysis. Please let me know what kind of information you are looking for.

Re: Total row Straight table Percentage% calculation

Thu, 28 Jul 2016 12:06:02 GMT

I have understood your suggestion now, took some time and research to get a clear picture

My problem here is that I have restricted the values displayed to the first 10 using Dimension Limits, so using TOTAL will get the overall value and not for the 10 values displayed. Is there a way to relate the TOTAL with the values displayed?

Re: Total row Straight table Percentage% calculation

Thu, 06 Oct 2016 19:09:31 GMT

Thanks Antonio

I was just looking for this!!

Awesome