Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Accumulation in Crosstab Pivot

I currently have a crosstab pivot like the following:

Company
Current1 Month2 Months3 Months4 Months5 Months6 Months7 Months8 Months9 Months10 Months11 Months and Over
ABC
121110987654321














                                                                                    
Company
Current1 Month2 Months3 Months4 Months5 Months6 Months7 Months8 Months9 Months10 Months11 Months and Over
ABC
121110987654321

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
Current1 Month2 Months3 Months4 Months5 Months6 Months7 Months8 Months9 Months10 Months11 Months and Over
ABC
7866554536282115            10              6                 31














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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

1 Reply
Not applicable
Author

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