Accumulation in Crosstab Pivot

Wed, 11 Jan 2012 12:12:33 GMT

I currently have a crosstab pivot like the following:

Company	Current	1 Month	2 Months	3 Months	4 Months	5 Months	6 Months	7 Months	8 Months	9 Months	10 Months	11 Months and Over
ABC	12	11	10	9	8	7	6	5	4	3	2	1

Company		Current	1 Month	2 Months	3 Months	4 Months	5 Months	6 Months	7 Months	8 Months	9 Months	10 Months	11 Months and Over
ABC		12	11	10	9	8	7	6	5	4	3	2	1

Each of the cells under the month bucket displays what happened that month using this formula:

sum([WIP Labor])+sum([WIP Burden])+sum([Part Burden])+sum([Part Labor])+sum([WIP Material])+sum([WIP Sub])

What I am trying to do is have these numbers build from the oldest to newest. I want it displayed like this:

Company	Current	1 Month	2 Months	3 Months	4 Months	5 Months	6 Months	7 Months	8 Months	9 Months	10 Months	11 Months and Over
ABC	78	66	55	45	36	28	21	15	10	6	3	1

I am having trouble finding something that works due to the Full Accumulation not being available in a pivot table.

Anyone know something that would work?

Update:

I tried using this expression but it does not seem to be working:

rangesum(sum(TOTAL [WIP Labor]),after([Month Bucket])) +
rangesum(sum(TOTAL [WIP Burden]),after([Month Bucket])) +
rangesum(sum(TOTAL [Part Burden]),after([Month Bucket])) +
rangesum(sum(TOTAL [Part Labor]),after([Month Bucket])) +
rangesum(sum(TOTAL [WIP Material]),after([Month Bucket])) +
rangesum(sum(TOTAL [WIP Sub]),after([Month Bucket]))

Re: Accumulation in Crosstab Pivot

Wed, 11 Jan 2012 17:09:34 GMT

I got it to work using this formula:

rangesum(after(TOTAL sum(if([Date Jobe Closed]>[Month Bucket],0,if([Labor Type]='I',0,[WIP Labor]))),0,count(TOTAL DISTINCT [Month Bucket])))  +
rangesum(after(TOTAL sum(if([Date Jobe Closed]>[Month Bucket],0,if([Labor Type]='I',0,[WIP Burden]))),0,count(TOTAL DISTINCT [Month Bucket])))  +
rangesum(after(TOTAL sum(if([Date Jobe Closed]>[Month Bucket],0,if([Labor Type]='I',0,[Part Burden]))),0,count(TOTAL DISTINCT [Month Bucket])))  +
rangesum(after(TOTAL sum(if([Date Jobe Closed]>[Month Bucket],0,if([Labor Type]='I',0,[Part Labor]))),0,count(TOTAL DISTINCT [Month Bucket])))  +
rangesum(after(TOTAL sum(if([Date Jobe Closed]>[Month Bucket],0,if([Labor Type]='I',0,[WIP Material]))),0,count(TOTAL DISTINCT [Month Bucket])))  +
rangesum(after(TOTAL sum(if([Date Jobe Closed]>[Month Bucket],0,if([Labor Type]='I',0,[WIP Sub]))),0,count(TOTAL DISTINCT [Month Bucket])))

topic Re: Accumulation in Crosstab Pivot in QlikView

Accumulation in Crosstab Pivot

Re: Accumulation in Crosstab Pivot