pivot table use different dimension for total

[Anonymous]

Hello,

I'm trying to implement a pivot table which has the same appearance as this table:

Two dimensions: ship week and Time2Fail

		Time2Fail	1	2	3	4	5	6	7	8
ship week	ship count	Accu ship count
1	4	4	1	1				1
2	5	9		1	1		1
3	2	11		1
4	6	17			1	1		1
5	2	19	1
6	11	30	1			2			1	1
			3	3	2	0	1	1

expression1: The ship count is how many units shipped in a ship week.

expression2: The Accu Ship count is the accumulate ship count along the the ship week.

e.g. accu ship count for ship week 1 is 4, for ship week 2 is 4+5=9 and for ship week 3 is 9+2= 11 and so on

expression3: The last row is the sub total of the column Time2Fail, but only count up to the ship week.

e.g. for the Time2Fail = 1 column, sub total = 1+1+1 = 3 (to ship week = 6)

for the Time2Fail = 2 column, sub total = 1+1+1 = 3 (to ship week = 5)

for the Time2Fail = 3 column, sub total = 1 +1=2 (to ship week = 4)

for the Time2Fail = 4 column, sub total = 0 (to ship week = 3)

for the Time2Fail = 5 column, sub total = 1 (to ship week = 2)

for the Time2Fail = 6 column, sub total = 1 (to ship week = 1)

based on the data:   

ship week	ID	Time2Fail
1	1000	1
1	1001	2
1	1002
1	1003	6
2	1954	3
2	1958
2	1959	2
2	1967
2	1988	5
3	2845	1
3	2846
4	1323	3
4	1325
4	1427	4
4	1377
4	1388
4	1399	6
5	6778	1
5	6779
6	6770	1
6	8912
6	1345	4
6	4523
6	4524
6	9923
6	9924	7
6	9925
6	9926	4
6	9927
6	9928	8

Thanks,

Josh

[Anonymous]

Now I know how to attach.

Here are the files.

[sunny_talwar]

Here is the script

Table:

LOAD *

from LifeData.qvd (qvd);

FinalTable:

LOAD *,

  AutoNumber([Ship Date]) as RowNo

Resident Table

Order By [Ship Date];

DROP Table Table;

Dimensions

Ship Date

=Aggr(Count(ID), [Ship Date])

=Aggr(RangeSum(Above(Count(ID), 0, RowNo())), RowNo)

Time2FWeek

Expression

=If(Dimensionality() = 0,

Sum(Aggr(If(Max(TOTAL [Ship Date]) - Time2FWeek + 1 >= [Ship Date], Count(ID)), [Ship Date], Time2FWeek)),

Count(ID))

[Anonymous]

Sunny T,

Hooray!! you are really a genius.

I made a change to use Ship Week instead of Ship Date to fix the subtotal problem.

Now I want to create a chart based on this table.

The chart has ship week as dimension and the expression will be (Time2Week total) / (Accu. Ship Count)

For example,

When x=Time2FWeek=1, the total of Time2FWeek=1 is 6, and the Accu, Ship Count is 24847 (Ship Week = 52)

So, the y-axis value = 6/24847 = 0.024%

When x=Time2FWeek=2, the total of Time2FWeek=2 is 7, and the Accu, Ship Count is 22898 (Ship Week = 51)

So, the y-axis value = 7/22898 = 0.030%

...

When x=Time2FWeek=52, the total of Time2FWeek=52 is Null, and the Accu, Ship Count is 163 (Ship Week = 1)

So, the y-axis value = NA/22898 = Null

The is expression I try and it seems not correct.

Sum(Aggr(If(Max(TOTAL [Ship Week]) - Time2FWeek + 1 >= [Ship Week],Count({<Time2FWeek-={'NA'}>} ID)), [Ship Week], Time2FWeek))/ RangeSum(Above(Count(ID), 0,Time2FWeek))

The qvw is attached.

Josh