Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jchang_snwl
New Contributor II

pivot table use different dimension for total

Hello,

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

Two dimensions: ship week and Time2Fail

     

Time2Fail12345678
ship weekship countAccu ship count
14411 1
259 11 1
3211 1
4617 11 1
52191   
611301 2 11
332011

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 weekIDTime2Fail
110001
110012
11002
110036
219543
21958
219592
21967
219885
328451
32846
413233
41325
414274
41377
41388
413996
567781
56779
667701
68912
613454
64523
64524
69923
699247
69925
699264
69927
69928

8

Thanks,

Josh

1 Solution

Accepted Solutions

Re: pivot table use different dimension for total

This?

Capture.PNG

Expression:

=If(Dimensionality() = 0,

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

Count(ID))

12 Replies

Re: pivot table use different dimension for total

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

Capture.PNG

jchang_snwl
New Contributor II

Re: pivot table use different dimension for total

Sunny T,

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

pivot.PNG

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

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

Re: pivot table use different dimension for total

This?

Capture.PNG

Expression:

=If(Dimensionality() = 0,

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

Count(ID))

jchang_snwl
New Contributor II

Re: pivot table use different dimension for total

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:

implement.PNG

How to fix it?

Appreciate in advance.

Josh

Re: pivot table use different dimension for total

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

jchang_snwl
New Contributor II

Re: pivot table use different dimension for total

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.

RowNoChange.PNG

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

resultRowNumber.PNG

Re: pivot table use different dimension for total

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

jchang_snwl
New Contributor II

Re: pivot table use different dimension for total

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

Community Browser