topic Re: Re: % calculation in a straight table in QlikView

% calculation in a straight table

Anonymous — Tue, 02 Sep 2014 19:02:53 GMT

Hi, I am trying to calculate the % breakout per the yellow highlight in the screenshot below in a straight table. Can anyone help please?

Thanks.

Re: % calculation in a straight table

JonnyPoole — Tue, 02 Sep 2014 19:04:53 GMT

try hitting the 'relative' checkbox on the expressions tab of the chart properties. it will render the values as percent of total

Re: % calculation in a straight table

Anonymous — Tue, 02 Sep 2014 19:12:37 GMT

Sorry, this is what i am trying to achieve.

Thanks

Re: % calculation in a straight table

Anonymous — Tue, 02 Sep 2014 19:14:33 GMT

Hi, thanks. Relative works but i am trying to get to achieve this. The % rates are off if I use relative.

Thanks again.

Re: % calculation in a straight table

JonnyPoole — Tue, 02 Sep 2014 19:15:00 GMT

no problem. to get a % subtotal you would want the following expression. Then format to a % on the number tab.

sum( [Total Revenue]) / sum( total <Group> [Total Revenue])

Re: % calculation in a straight table

Anonymous — Tue, 02 Sep 2014 19:21:03 GMT

I am getting nulls...

Re: Re: % calculation in a straight table

JonnyPoole — Tue, 02 Sep 2014 19:26:09 GMT

Can you open this example ?

Re: % calculation in a straight table

Anonymous — Tue, 02 Sep 2014 19:28:45 GMT

I am using personal edition....

Re: % calculation in a straight table

JonnyPoole — Tue, 02 Sep 2014 19:36:52 GMT

If you have PE , then please upload your QVW or a sample QVW and i will tweak the expressions. I will copy/paste the expressions and any other tweaks to get it to work so that you can manually apply to your own QVW. sound good ?

Re: % calculation in a straight table

Anonymous — Tue, 02 Sep 2014 20:25:40 GMT

Hi I am having a hard time attaching the qvw, might have been a little big. I thought it was uploaded but I am not able to see it. Can you see it?

Thanks,

Re: % calculation in a straight table

JonnyPoole — Tue, 02 Sep 2014 20:38:50 GMT

i do not see it. if you believe its too big try the following:

1. File -> SaveAs (new file name)

2. Open the load script, hit 'debug', on the left change to 'limited load' and select 10000 or 1000

3. hit run to run the script

4. once complete save the new file and upload

Re: Re: % calculation in a straight table

Anonymous — Tue, 02 Sep 2014 20:45:45 GMT

Great, here you go...

Thanks!

Re: % calculation in a straight table

JonnyPoole — Tue, 02 Sep 2014 20:48:21 GMT

Got it. The [Total Revenue] expression is a calculated expression so you need to put that in the numerator/denominator like this:

=sum(if(Dataset='Recent',Revenue))/ sum( TOTAL <Segment_Classification> if(Dataset='Recent',Revenue) )

Re: Re: % calculation in a straight table

Anonymous — Tue, 02 Sep 2014 20:51:16 GMT

oh.. I did that but I used Total Revenue instead of Revenue... let me try it now.

Thanks!

Re: Re: % calculation in a straight table

Anonymous — Tue, 02 Sep 2014 20:57:50 GMT

it works! Thanks, I have another question, if I want to do the same but by seg class by BU. Would that work? Say total % by BU should 100% for each of the BU.

Thanks

Re: Re: % calculation in a straight table

JonnyPoole — Tue, 02 Sep 2014 21:08:53 GMT

it should, just change the field after the TOTAL clause in the denominator to the field that represents the subtotal.

change:

=sum(if(Dataset='Recent',Revenue))/ sum( TOTAL <Segment_Classification> if(Dataset='Recent',Revenue) )

to:

=sum(if(Dataset='Recent',Revenue))/ sum( TOTAL <BU> if(Dataset='Recent',Revenue) )

Re: Re: % calculation in a straight table

Anonymous — Tue, 02 Sep 2014 21:56:08 GMT

Thank you!

Re: Re: % calculation in a straight table

Anonymous — Tue, 02 Sep 2014 22:10:20 GMT

Sorry.. one more question - the BU column is also a cyclic group that toggles between BU and segment. If i use that script, I can only get to BU calc, what about the segment calc?

Thank you so much for your help!

Re: % calculation in a straight table

JonnyPoole — Wed, 03 Sep 2014 01:14:02 GMT

You can do that. I'm not in front of my computer so I can't remember what your cyclic group name was but here is what it may look like:

if ( Getcurrentfield([groupname)] = 'BU' , sum(if(Dataset='Recent',Revenue))/ sum( TOTAL <BU>if(Dataset='Recent',Revenue) ) , If ( getcurrentfield([groupname]) = 'Segment_Classification' , sum(if(Dataset='Recent',Revenue))/ sum( TOTAL <Segment_Classification> if(Dataset='Recent',Revenue) )))

Where groupname is the name of your cycle group.

Re: % calculation in a straight table

Anonymous — Wed, 03 Sep 2014 13:24:41 GMT

Great! It works, thanks!