topic Re: very very important....how to add a row in pivot table in QlikView

very very important....how to add a row in pivot table

Fri, 27 Apr 2012 07:07:16 GMT

i am creating a pivot table in which i am taking a column from database as row. from database i am getting six different rows but i want to add 2 more rows showing the total of these 8 rows and their count.

how can i do that.....plz help me

very very important....how to add a row in pivot table

Anonymous — Fri, 27 Apr 2012 10:27:02 GMT

I don't see how to add two total rows, but you can show both sum and count in one row, for example by using expression like this:
if(rowno()>0,
sum(Price)
, 'sum=' & sum(Measure) &'; ' & 'count=' & count(Measure))

very very important....how to add a row in pivot table

Fri, 27 Apr 2012 11:01:28 GMT

could u be more specific plz...tell me where to write that expression and how to show which is count and which is sum???

very very important....how to add a row in pivot table

Fri, 27 Apr 2012 11:14:53 GMT

in addition to it i want to know whether i can add values in the dimension fetched from database,while using it in the pivot table.

in mycase i need to sum of amount of top10 and for others....

Re: very very important....how to add a row in pivot table

Anonymous — Sat, 28 Apr 2012 19:37:37 GMT

Attaching an example.

Chart 1 contains expressions for sum (total) and count. Chart 2 - only sum, but both "total" and count are in the total row.

It is not adding two rows, but providing both totals in one row.

Re: very very important....how to add a row in pivot table

Mon, 30 Apr 2012 06:49:29 GMT

hi Michael

this is not what i am looking for...in my case i am applying logic on every value of the dimension to get the desired results in the columns...

regards

Vitul

Re: very very important....how to add a row in pivot table

Anonymous — Tue, 01 May 2012 01:36:32 GMT

Every value? - than the chart 1 is what you need.

Or maybe chart 3 - see attched.

Re: very very important....how to add a row in pivot table

Wed, 02 May 2012 05:01:07 GMT

hi Michael

thnx for your help....

this is not what i am liikong for....let me explain in more detail....for that i am goin to attach an image..it wud be easier for u to understand that....

here in image...i am using a column 'provider' as a dimension from the database and i want the top 10 provider on the basis of another column 'amount'.

after that i have to show the sum of top 10 in the pivot table and sum of the rest of providers in another row..tell me how to achieve it plz???

Re: very very important....how to add a row in pivot table

Wed, 02 May 2012 05:03:36 GMT

hi michael and theses values jp,rs,shine etc are coming from database and it will keep on changing on the basis of the 'amount'....

regards

Vitul

Re: very very important....how to add a row in pivot table

Anonymous — Wed, 02 May 2012 13:51:30 GMT

Vitul,

This is certainly possible, with a minor exception - "all other" is above "total of top 10".
Here is how to implement it:
1. Your dimension should be a calculated dimension, using rank() function based on the sum of Amount (or whatver expression you need), which filters out dimensions values with rank<10. It is dynamic, changes after reload as data changes. Do not check "Suppress When Value Is Null" - it will keep your "all others".
2. On the Presentation tab, replace default "Null symbol" with the text "all others"
3. Expressions will be conditional, based on rank, and different for total. The logic is like this:
If it is not "total" line, use expression as is (e.g. sum(Amount))
if it is the total line, the expression is conditional and includes only values if dimension has rank>10 (e.g. sum(<rank-based conditions using if or set analysis> Amount)).
I don't have time for an example now. Will get to it later, unless somebody else gives you an example before that.

Regards,
Michael

Edit: See atachment, chart 4. I think it is very close to your requirement.

very very important....how to add a row in pivot table

Anonymous — Wed, 02 May 2012 23:46:07 GMT

Vitul, see new attachment in my post above. I'm not sure you're notified without this additional post.

very very important....how to add a row in pivot table

Thu, 03 May 2012 11:27:07 GMT

hi thnx for your solution....

ihave 1 more problem in my document....when i am using a calculated dimension...the raanking is done on the basis of a column named membercount and the totals and all others must be calculated on the basis of salary...i am having problem in executing that condition in the expression

plz help asap....

regards

Vitul

very very important....how to add a row in pivot table

Anonymous — Thu, 03 May 2012 17:38:32 GMT

It is not a problem at all. You can use rank based on one expression in any other expression. In general, it may like this (using my earlier example):

if(rowno()>0,
<any expression>,
sum(if(aggr(rank(sum(Measure),4),Dimension)<5, aggr(<any expression>,Dimension),0))
)

very very important....how to add a row in pivot table

Fri, 04 May 2012 04:49:13 GMT

in my case...ist i am pulling a columnn named provider on the basis of a column called ecount...for it i am using calculated dimension

i.e aggr(if(rank(sum(ecount/3),4)<51,provider),provider)......i need top 50 provider on the basis of ecount....

now in the expression i had to use allowed amount as a measure means i had to sum(amount)...

i am using

if(rowno()>0,sum({<quarter={1},year={'P'},key={'A'}>}amount),sum(if(aggr(rank(sum(ECOUNT/3),4),provider)<51,aggr(

sum({<quarter={1},year={'P'},key={'A'}>}amount),0)))

now the problem is that i want to show the sum of total amount for top 50 as well as all others...where i have used

ECOUNT/3 this part of expression will evaluate amount for top 50...but how to write it???

in your case you were ranking and evaluating the expression on only 1 measure...here first i am ranking on ecount and then evaluating on the basis of amount......

plz help asap....

regards vitul

very very important....how to add a row in pivot table

Fri, 04 May 2012 06:03:50 GMT

when i am using the expression according to your suggestionits evaluating 0 for the top50......

i am not able to understand it why????

very very important....how to add a row in pivot table

Fri, 04 May 2012 11:48:30 GMT

hi Michael

there is one more requirement in pivot table....i have to add another row membership along with top 50 providers,

total for top50,total of all others and total of all....

from the database only top50 are coming....the formula for membership is different.....plz tell me if it is possible in pivot table..if yes...then explain me with an example plz.....

HERE FORMULA TO CALCULATE MEMBERSHIP IS DIFFERENT AND REST OF THE FORMULAS ARE SAME AS WE HAVE DISUSSED IN EARLIER DISCUSSIONS...

HOW TO EVALUATE TWO DIIFERENT FORMULAS IN A SINGLE COLUMN THEN??

Re: very very important....how to add a row in pivot table

Anonymous — Sat, 05 May 2012 18:59:18 GMT

Vitul,

I don's see how to add another line, except the same way I described earlier. See charts 5 and 6 in attched - it is a combination of solutions in charts 2 and 4 and 2 and 3.

Regards,

Michael

PS: i hope you have enough information now to figure out what is possible.

very very important....how to add a row in pivot table

Thu, 17 May 2012 04:47:08 GMT

hi Michael

i have used your solution in my approach and i am getting desired results......but i am facing one problem with your solution...

the situation is like this...

sum(if(aggr(rank(sum(amount),4),provider)<11,aggr(sum(pd_amnt),provider),0)))

this is working fine....

but in some cases i have to divide pd_amnt with another measure count...how can i achieve that in aggr function??/

sum(if(aggr(rank(sum(amount),4),provider)<11,aggr(sum(pd_amnt),provider)/sum(count),provider),0)))

i have used it but its not working.

thnx and regards

Vitul

Re: very very important....how to add a row in pivot table

Anonymous — Fri, 18 May 2012 00:55:19 GMT

Not sure I can get it right without knowing the full picture. Anyway, my guess is:

sum(if(aggr(rank(sum(amount),4),provider)<11,aggr(sum(pd_amnt)/sum(count),provider),0)))

very very important....how to add a row in pivot table

Fri, 18 May 2012 09:41:52 GMT

still its not wrking i have tried it.....the report which i am developing in it i have to show the total of top50,total of all other and also total of all of the PD-amnt in the same pivot table....is it fissible in pivot table???