how to solve the pivot table problem

[#1]

Hi,

I have some sample data in pivot table is given below

month	status	grandtotal
jan	lst	40
	nd	85
	wn	125
FEB	lst	36
	nd	87
	wn	113

i need in given below  format

month	lst	nd	wn	grandtotal
jan	40	30	3	73
feb	45	35	4	84
mar	65	45	5	115
grandtotal	150	110	12	272

any one please help me..................

[JonnyPoole]

Here is my solution:

Data:

month	status	grandtotal
JAN	lst	40
	nd	85
	wn	125
FEB	lst	36
	nd	87
	wn	113

Load Script:

Months:

load Distinct

  rowno() as Record,

  month

FROM

(ooxml, embedded labels, table is Sheet1)

where isnull(month) <> - 1;

left join (Months)

load

  ceil(RecNo()/3) as Record,

  status,

  grandtotal

FROM

(ooxml, embedded labels, table is Sheet1);

Screenshot:

[#3]

Thnak you so much  but i need

the pivot table is generated in sheet level(in dashboard)

month	status	grandtotal
jan	lost	40
	nodecision	85
	won	125
FEB	lost	36
	nodecision	87
	won	113

like this.

after change the data in the given format i required

month	lost	nodecision	won	grandtotal
jan	40	30	3	73
feb	45	35	4	84
mar	65	45	5	115
grandtotal	150	110	12	272

[#4]

how to write  scripting after generating the pivot table in dashboard is it possible plz tell me......

[JonnyPoole]

You can't reverse engineer a pivot table into script.

If your months are reported on the 'middle row' of each block like this:

month	status	grandtotal
	lst	40
jan	nd	85
	wn	125
	lst	36
feb	nd	87
	wn	113

then tweak the script slightly to this:

Months:

load Distinct

  ceil(recno()/3) as Record,

  month

FROM

(ooxml, embedded labels, table is Sheet1)

where isnull(month) <> - 1;

left join (Months)

load

  ceil(RecNo()/3) as Record,

  status,

  grandtotal

FROM

(ooxml, embedded labels, table is Sheet1);

[#6]

sorry jonathan can't understand.i m taking data from .qvd file. the .qvd file loaded into qlikview  i need to generate the pivot table.  i generate pivot.  the pivot table is  like this.

month	status	grandtotal
jan	lost	40
	nodecision	85
	won	125
FEB	lost	36
	nodecision	87
	won	113

the grand total column generated after created pivot table not their in before.

but my required pivot table is

month	lost	nodecision	won	grandtotal
jan	40	30	3	73
feb	45	35	4	84
mar	65	45	5	115
grandtotal	150	110	12	272

how plz tell me.....

[JonnyPoole]

drag the 'status' field to the columns as below.  Then go to the chart properties / presentation tab, select month and enable 'partial sums' and repeat for status as well.  Please note that the math in your desired output does not reflect the data ...  you need to explain why if the totals in the desired output are indeed correct.

[#8]

k Thanks Jonathan in that I need one more column like % column means won/total=%(column). how to add the column to pivot table plz explain me....

[JonnyPoole]

You can add a 2nd expression on the expression tab with this formula:

= sum(  grandtotal) / sum(  total  grandtotal)

However this will add a % of total for all status'.  Give it a try and see if you like it. If you need it to be more specific then read on:

To only have  '% Won' is a little more involved. One way is as follows:

1. Create a new dimension based on status that adds '% Won' as a status

2. Alter the expression to calculate a % of total won for the '% Won' column

Here is a sample load script with 3 steps.  On the chart, replace 'status' with 'statusforchart' as the dimension, change the sort order of statusforchart to be 'expression and select 'statussort'. then alter the expression to:

if (statusforchart='% Won' , num( sum( {$<status={wn}>} grandtotal) / sum( {$<status={wn}>} total  grandtotal),'#,###.0%') ,num( sum(grandtotal),'#,###,####'))

LOAD SCRIPT:

Months:

LOAD month,

     status,

     grandtotal

FROM

(ooxml, embedded labels, table is Sheet1);

Status:

load Distinct

  status,

  status as statusforchart,

  if (status = 'lost',1, if(status = 'nodecision', 2 , if (status = 'wn',3))) as statussort

Resident Months;

Concatenate (Status)

load

  'wn' as status,

  '% Won' as statusforchart,

  4 as statussort

AutoGenerate 1;

[#10]

Hi Jonathan,

Actually  my problem is i have .qvd file data. data the used to generate the pivot table like this 

month	lost	nodecision	won	grandtotal
jan	40	30	3	73
feb	45	35	4	84
mar	65	45	5	115
grandtotal	150	110	12	272

but i need this type

month	lost	nodecision	won	grandtotal	Rate%
jan	40	30	3	73	won/Grandtotal
feb	45	35	4	84	won/Grandtotal
mar	65	45	5	115	won/Grandtotal
grandtotal	150	110	12	272	won/Grandtotal

my using the this expression count({<status={'won'}>}[month])/count(status)

it will generate like this but i need only rate% column after the grand total only

month	lost		nodecision		won		grandtotal	Rate%
	grandtotal	Rate%	grandtotal	Rate%	grandtotal	Rate%
jan	40		30		3		73
feb	45		35		4		84
mar	65		45		5		115
grandtotal	150		110		12		272