
Re: pivot table use different dimension for total
Sunny Talwar Sep 28, 2016 3:14 PM (in response to Josh Chang)
Community_234422.qvw 150.5 K

Re: pivot table use different dimension for total
Josh Chang Sep 28, 2016 4:46 PM (in response to Sunny Talwar )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 Sep 28, 2016 4:50 PM (in response to Josh Chang)I see what you mean. Let me play around with it

Re: pivot table use different dimension for total
Sunny Talwar Sep 28, 2016 5:10 PM (in response to Josh Chang)This?
Expression:
=If(Dimensionality() = 0,
Sum(Aggr(If(Max(TOTAL [ship week])  Time2Fail + 1 >= [ship week], Count(ID)), [ship week], Time2Fail)),
Count(ID))

Community_234422.qvw 151.5 K

Re: pivot table use different dimension for total
Josh Chang Sep 28, 2016 6:02 PM (in response to Sunny Talwar )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 Sep 28, 2016 8:47 PM (in response to Josh Chang)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
Josh Chang Sep 29, 2016 2:50 PM (in response to Sunny Talwar )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 Sep 29, 2016 2:55 PM (in response to Josh Chang)Would you be able to share the new application to take a look at?

Re: pivot table use different dimension for total
Josh Chang Sep 29, 2016 5:51 PM (in response to Sunny Talwar )I can share the qvw but don't know how to attach.

Re: pivot table use different dimension for total
Josh Chang Sep 29, 2016 5:55 PM (in response to Sunny Talwar )Now I know how to attach.
Here are the files.

LifeData.qvd 1.8 MB

Test.qvw 535.2 K

Re: pivot table use different dimension for total
Sunny Talwar Sep 29, 2016 6:36 PM (in response to Josh Chang)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))

Test.qvw 474.0 K

Re: pivot table use different dimension for total
Josh Chang Sep 29, 2016 7:52 PM (in response to Sunny Talwar )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 yaxis 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 yaxis 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 yaxis 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

Test.qvw 475.5 K









