topic Re: How to add extra calculated rows? in QlikView

How to add extra calculated rows?

Wed, 26 Sep 2012 13:01:47 GMT

hi, i have a 2-D (State and Group) pivot table showing count, as below:

State Group g1 g2 g3 Total

Total 100 200 300 600

s1 90 190 285 565

s2 10 10 15 35

now i want to add one row at bottom to calculate percentage of State s2 out of Total per Group, like below:

State Group g1 g2 g3 Total

Total 100 200 300 600

s1 90 190 285 565

s2 10 10 15 35

s2 % 10% 5% 5%

is this possible and can anyone tell how to do it?

or any other alternatives?

thanks,

little

Re: How to add extra calculated rows?

Anonymous — Thu, 27 Sep 2012 20:46:51 GMT

hi Litlle , as per my understanding find the solution with the attached. Let me know if that helps....

Re: How to add extra calculated rows?

Fri, 28 Sep 2012 09:43:57 GMT

vinay_bangari wrote:
hi Litlle , as per my understanding find the solution with the attached. Let me know if that helps....

Hi Little, add vinay's point. Perhaps it would meet ur requirement if turning to use pivot table and switch row and column.

Re: How to add extra calculated rows?

Fri, 28 Sep 2012 09:44:08 GMT

vinay_bangari wrote:
hi Litlle , as per my understanding find the solution with the attached. Let me know if that helps....

Hi Little, add vinay's point. Perhaps it would meet ur requirement if turning to use pivot table and switch row and column.

Re: How to add extra calculated rows?

Fri, 28 Sep 2012 10:26:16 GMT

Thank you Mike and Vinay, your response are very helpful.

But what if my data input is not cross table but flat like below?

Basically, the problem is the cross view generated out of below data won't have fixed dimension field names, as they are coming from distinct of data.

Identifier	Group	State
2	g1	s1
3	g1	s2
4	g2	s1
5	g2	s2
6	g1	s1
7	g1	s2
8	g2	s1
9	g2	s2
10	g1	s1
11	g1	s1

Re: How to add extra calculated rows?

Anonymous — Fri, 28 Sep 2012 11:05:14 GMT

Hi,

You could add a row into your State table with 'ZZZ' (not very pretty I know*) to appear in your table as the last row.

You could then change the calculation for your columns to say:

if(Bottom(TOTAL count(YourCounterField)),Num(count({$<State={'s2'}>}g1)/aggr(count(g1),'Group'),'##%')) // %age of State/Group combo out of Total per Group

For this new row to appear you'll need to ensure you've not ticked Suppress Zero-Values or Suppress Missing in the Presentation tab.

Regards,

Jonathan

*Alternatively could ensure States are loaded in correct alphabetical order, and then add a new row called 'Percentage' later and then sort by 'Load Order'

Re: How to add extra calculated rows?

Fri, 28 Sep 2012 15:55:07 GMT

thank you. wrt adding the row, can we configure it from data loading process (qlikview side)? As we intend to provide data thru a web service, and adding dummy row there might not be feasible.

the whole idea is: does QlikView provide some simple Excel-like funtions, such as this one - adding a row computing from other rows?

Re: How to add extra calculated rows?

Anonymous — Mon, 01 Oct 2012 08:06:49 GMT

Hi,

Not sure I understand the scanerio exactly, but after you've done all your loads from web services etc you could concatenate a row onto the internal QV table.

Jonathan

Re: How to add extra calculated rows?

jagan — Mon, 01 Oct 2012 09:36:14 GMT

Hi,

Hope attached file helps you, check that is what you required.

Regards,

Jagan.

Re: How to add extra calculated rows?

sujeetsingh — Mon, 01 Oct 2012 10:01:28 GMT

Hey plz explain the senerio.

Re: How to add extra calculated rows?

Mon, 01 Oct 2012 10:12:07 GMT

thank you all,

but my data is flat, as below:

Identifier	Group	State
2	g1	s1
3	g1	s2
4	g2	s1
5	g2	s2
6	g1	s1
7	g1	s2
8	g2	s1
9	g2	s2
10	g1	s1
11	g1	s1

then in my veiw, i create below 2-D cross table:

State	Group	g1	g2	Total
s1		4	2	6
s2		2	2	4
Total		6	4	10

and what i want to add is to this 2-D cross table:

State	Group	g1	g2	Total
s1		4	2	6
s2		2	2	4
Total		6	4	10
s2 %		33%	50%	40%

Re: How to add extra calculated rows?

jagan — Mon, 01 Oct 2012 10:24:30 GMT

Hi,

Did you checked the attachment?

Regards,

Jagan.

Re: How to add extra calculated rows?

Mon, 01 Oct 2012 10:26:54 GMT

yes Jagan. seems like your example is only with 1 dimension, but mine is 2-D cross table.

Re: How to add extra calculated rows?

jagan — Mon, 01 Oct 2012 10:33:07 GMT

Hi,

Can you attach sample file that you are working? It helps in solving the problem.

Regards,

Jagan.

Re: How to add extra calculated rows?

Mon, 01 Oct 2012 10:45:47 GMT

PFA.

Thanks,

Sean

Re: How to add extra calculated rows?

jagan — Mon, 01 Oct 2012 11:01:48 GMT

Hi,

If you 2 states then above attached file is the best option, otherwise you need to create another chart to display percentage alone.

Hope this helps you.

Regards,

Jagan.

Re: How to add extra calculated rows?

Tue, 09 Sep 2014 08:53:01 GMT

hi , littleyoung how did u get your respond about s2%?