topic Re: how to do percent to total/subtotal in QlikView

how to do percent to total/subtotal

Anonymous — Thu, 22 Dec 2016 00:20:58 GMT

hello

in the picture below.. I have a column called total units.

with sample data u can see dresses+skirts I have a formula

=pick(Dimensionality(),null(),sum(sales)/sum(Total sales), sum(sales)/sum(Total <[2bc]> sales))

this give me percent to the total sales 700...

question 1.. how do I get it to percent to subtotal (dress is 600)

question 2 how do I get cut and sew total sales 600 to percent to grand total of 700?

issue 2 is

if u look at the wholesale percent to total column.. its giving me percents for ALL the body class.. even though only 3 have sales units in it...

im not sure how to get it for data with only sales.. below is the formula I used

=pick(Dimensionality(),null(),sum([2gsp])/sum(Total [2gsp]), sum([2gsp])/sum(Total <[2bc]> [2gsp]))

thanks to all

Re: how to do percent to total/subtotal

sunny_talwar — Thu, 22 Dec 2016 02:06:30 GMT

For first issue, may be this:

=Pick(Dimensionality(), Sum(sales)/Sum(Total sales), Sum(sales)/Sum(TOTAL <[2bc]> sales))

For issue 2

What is your expression for Total Net Wholesale columne?

Re: how to do percent to total/subtotal

Anonymous — Thu, 22 Dec 2016 14:14:21 GMT

the wholesale one is

=Sum(Aggr(Sum({<[style],[stylecol]>} sales) * Sum(Aggr(Sum([2gsp]), [style])),[style], [stylecol]))

Re: how to do percent to total/subtotal

sunny_talwar — Thu, 22 Dec 2016 14:18:11 GMT

May be this:

=Sum(Aggr(Sum({<[style],[stylecol]>} sales) * Sum(Aggr(Sum([2gsp]), [style])),[style], [stylecol]))/Sum(TOTAL <[2bc]> Aggr(Sum({<[style],[stylecol]>} sales) * Sum(Aggr(Sum([2gsp]), [style])),[style], [stylecol]))

Re: how to do percent to total/subtotal

Anonymous — Fri, 23 Dec 2016 17:59:05 GMT

hello

its strange..

when I do my current formula

=pick(Dimensionality(),null(),sum([2gsp])/sum(Total [2gsp]), sum([2gsp])/sum(Total <[2bc]> [2gsp]))

the screen shows all data

but when I uses your formula

1 the answer isnt quite right... see the first line it says 100% total when it should be able 80 something

2 it forces all the blank or zero sum stuff to hide...when it should just show zero like the original formula

thanks and happy holidays

Re: how to do percent to total/subtotal

sunny_talwar — Fri, 23 Dec 2016 18:06:40 GMT

My bad, I think you needed this:

=Sum(Aggr(Sum({<[style],[stylecol]>} sales) * Sum(Aggr(Sum([2gsp]), [style])),[style], [stylecol]))/Sum(TOTAL <[2bfasefg]> Aggr(Sum({<[style],[stylecol]>} sales) * Sum(Aggr(Sum([2gsp]), [style])),[style], [stylecol]))

Re: how to do percent to total/subtotal

Anonymous — Fri, 23 Dec 2016 18:27:27 GMT

u my good man are a genius..

and I realize I had suppress zero values..

that's why..

genius

thanks so much and happy holidays

Re: how to do percent to total/subtotal

sunny_talwar — Fri, 23 Dec 2016 18:29:14 GMT

Thanks my friend. Happy holidays to you too ...