Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]))
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])))
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])))