How to add extra calculated rows?

[#1]

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

[Anonymous]

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

[#3]

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.

[#4]

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.

[#5]

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

[Anonymous]

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'

[#7]

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?

[Anonymous]

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

[jagan]

Hi,

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

Regards,

Jagan.

[sujeetsingh]

Hey plz explain the senerio.