topic Re: Percentage Subtotal in QlikView

Percentage Subtotal

kfahri342 — Tue, 26 Sep 2017 12:29:26 GMT

Hi All,

Is there a way to create table like this on Qlikview.

I need to be able to show the count Total and % Total of this table [i.e. the last two rows]. Is this achievable?

Many Thanks

Kozan

Name	Week 1	Week 2	Week 3	Grand Total
Steve	50	50	100	200
John	80	85	65	230
Emily	20	60	80	160
Grand Total	150	195	245	590
Grand Total [%]	25%	33%	42%	100 %

Re: Percentage Subtotal

sunny_talwar — Tue, 26 Sep 2017 12:39:20 GMT

Check the attached

Re: Percentage Subtotal

Sergey_Shuklin — Tue, 26 Sep 2017 12:40:25 GMT

Hello, Kozan!

You can create two similar tables with different calculation of totals and place them one under the other.

Re: Percentage Subtotal

kfahri342 — Tue, 26 Sep 2017 13:15:44 GMT

Hi this is exactly what I am looking for.

Can you explain the purpose of "dim". I don't have this value and can't figure out why you have included this in your expressions.

Cheers

Re: Percentage Subtotal

sunny_talwar — Tue, 26 Sep 2017 13:19:53 GMT

Dim is created as an Inline table and is not connected to anything in your database... This is basically used to create the two Grand Total rows

Pick(Dim, Name, 'Grand Total', 'Grand Total [%]')

So, when Dim = 1, Dimension is Name

when Dim = 2, Dimension is 'Grand Total'

when Dim = 3, Dimension is 'Grand Total [%]'

Re: Percentage Subtotal

Anonymous — Tue, 26 Sep 2017 13:51:06 GMT

If you use the pick, you can lose performance.

You can use text objects at the place of each field.

Re: Percentage Subtotal

sunny_talwar — Tue, 26 Sep 2017 13:55:29 GMT

Is that based on testing or you just think that Pick will reduce performance?

Also, what happens if you select a single name here? Emily... would the total % go up or down

Re: Percentage Subtotal

Anonymous — Tue, 26 Sep 2017 14:00:04 GMT

Nice Paulo

Re: Percentage Subtotal

Anonymous — Tue, 26 Sep 2017 14:09:32 GMT

It's real,

I already tested it in an application.

I used as pick as separated expressions... There is a lot of difference.(But I don't know explain the reasons why kkkk)

"Also, what happens if you select a single name here? Emily... would the total % go up or down"

-The total field don't move

Re: Percentage Subtotal

sunny_talwar — Tue, 26 Sep 2017 14:16:02 GMT

Would you be able to share an example where you can show the performance difference between your proposed solution vs pick() method?

Re: Percentage Subtotal

Anonymous — Tue, 26 Sep 2017 14:27:20 GMT

I can't share this example.

It's data from a customer in my company.

Sorry.

But you can test this in the future.

Re: Percentage Subtotal

sunny_talwar — Tue, 26 Sep 2017 14:28:42 GMT

Hahahaha I have done testing with this with large amount of data and it never gave me any problems

Re: Percentage Subtotal

Anonymous — Tue, 26 Sep 2017 14:35:57 GMT

Very strange kkkkk.. when I tested, with large amount of data, there was a big difference.

Re: Percentage Subtotal

sunny_talwar — Tue, 26 Sep 2017 14:37:02 GMT

Okay

Re: Percentage Subtotal

kfahri342 — Tue, 26 Sep 2017 16:04:16 GMT

Thanks Sunny.

Unfortunately, I am struggling to replicate what you have done on your qvw.doc [the example I shared with you guys was for demonstration purposes only. As the data I am using is work related and therefore unable to share, but its also a bit more complicated].

Are you able to talk me through a bit more on how your expression works please? As this is where I am failing to understand.

Also, please note the Values I receive are a result of a COUNT IF function, - can you let me know if there are any significant implications on the expression when using this?

Re: Percentage Subtotal

fallenangel6 — Tue, 04 Dec 2018 10:23:47 GMT

This reply saved my day. Thank you Stalwar1