topic Re: pivot table use different dimension for total in QlikView

pivot table use different dimension for total

Anonymous — Tue, 27 Sep 2016 22:14:00 GMT

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

Re: pivot table use different dimension for total

sunny_talwar — Wed, 28 Sep 2016 19:14:10 GMT

Not sure I understand expression 3, but is this what you wanted?

Re: pivot table use different dimension for total

Anonymous — Wed, 28 Sep 2016 20:46:21 GMT

Sunny T,

It's a wonderful answer except for expression 3. Thank you so much.

The expression 3 means the total is the partial total for those fall inside a step shape as depicted in the red line. So the total for column Time2Fail 4 should be 0 and 1 for column Time2Fail 6.

It will be appreciated if you can also provide an answer for expression 3.

Best,

Josh

Re: pivot table use different dimension for total

sunny_talwar — Wed, 28 Sep 2016 20:50:58 GMT

I see what you mean. Let me play around with it

Re: pivot table use different dimension for total

sunny_talwar — Wed, 28 Sep 2016 21:10:04 GMT

This?

Expression:

=If(Dimensionality() = 0,

Sum(Aggr(If(Max(TOTAL [ship week]) - Time2Fail + 1 >= [ship week], Count(ID)), [ship week], Time2Fail)),

Count(ID))

Re: pivot table use different dimension for total

Anonymous — Wed, 28 Sep 2016 22:02:51 GMT

Sunny T,

You are amazing!

Now I'm implementing the answer your provide to our actual data.

Turn out, the Accu Ship Count doesn't follow the the order of Ship Date.

More precise, the RowNo numbering is not based on the sorting order of Ship Date.

Here is a screenshot:

How to fix it?

Appreciate in advance.

Josh

Re: pivot table use different dimension for total

sunny_talwar — Thu, 29 Sep 2016 00:47:21 GMT

I would suggest to fix this in the script.

FactTable:

LOAD [Ship Date],

....

FROM Source;

LinkTable:

LOAD Distinct [Ship Date],

RowNo() as RowNo

Resident FactTable

Order By [Ship Date];

So, essentially create RowNo() in a new table where you order your Ship Date in ascending order.

HTH

Best,

Sunny

Re: pivot table use different dimension for total

Anonymous — Thu, 29 Sep 2016 18:50:21 GMT

Sunny T,

It's not working well if I use RowNo generated in script.

I used "RowNo() AS RowNumber" instead of "RowNo() AS RowNo" as described in your solution.

And I made the change accu. Ship Count expression according to the change from RowNo to RowNumber.

In our case, the table I showed you earlier actually is a result of filtering by couple list boxes.

The original table has thousands record. So, after filtering, the RowNumber is not contiguous and not starting from 1.

Here is the result. All 0's for Accu. Ship Count

Re: pivot table use different dimension for total

sunny_talwar — Thu, 29 Sep 2016 18:55:23 GMT

Would you be able to share the new application to take a look at?

Re: pivot table use different dimension for total

Anonymous — Thu, 29 Sep 2016 21:51:55 GMT

I can share the qvw but don't know how to attach.

Re: pivot table use different dimension for total

Anonymous — Thu, 29 Sep 2016 21:55:22 GMT

Now I know how to attach.

Here are the files.

Re: pivot table use different dimension for total

sunny_talwar — Thu, 29 Sep 2016 22:36:30 GMT

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))

Re: pivot table use different dimension for total

Anonymous — Thu, 29 Sep 2016 23:52:28 GMT

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