Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to get the cumulative bucket expression of a pivot to a dimension to form new pivot?

Hello,

I am working on a sales qlikview DB project. the  ask is to transform the below pivot chart(chart 1 ) (note- last 3 columns are derived using expressions) in a way so that last column ('bucket') becomes the dimension and other adjacent columns gets computed (like in chart 2).

chart 1     

PARTNER_IDSales( PERFORMANCE)% of Contribution to total sales% Cumulative Contribution Bucket- based on cumulative contirbution
189151381.14457817%17%TOP 20%
290131074.37291214%31%TOP 40%
23580938.325169112%43%TOP 50%
8456865.396533211%54%TOP 60%
8760707.41271039%63%TOP 70%
18200452.488816%68%TOP 70%
14795437.435736%74%TOP 80%
19142433.18497025%79%TOP 80%
8972374.081775%84%TOP 90%
8633370.713255%89%TOP 90%
28683201.444383%91%TOP 100%
5423201.105813%94%TOP 100%
19349128.407932%95%TOP 100%
19818126.80099352%97%TOP 100%
863186.350971%98%TOP 100%
878430.312930%99%TOP 100%

  Chart 2

Percentage# PartnersSell thru
Top 10%0$0
Top 20%1$1,381
Top 30%0$1
Top 40%1$1,074
Top 50%1$938
Top 60%1$865
Top 70%2$1,159
Top 80%2$870
Top 90%2$752
Top 100%6$572

any kind help/advice would be appreciated.

Thanks!

Avi

1 Solution

Accepted Solutions
sunny_talwar

Like this?

Capture.PNG

I hope you have QV12, because you will need to use this -> The sortable Aggr function is finally here!

Dimension:

=Aggr(

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.10, Dual('0-10%', 1),

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.20, Dual('0-20%', 2),

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.30, Dual('0-30%', 3),

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.40, Dual('0-40%', 4),

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.50, Dual('0-50%', 5),

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.60, Dual('0-60%', 6),

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.70, Dual('0-70%', 7),

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.80, Dual('0-80%', 8),

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.90, Dual('0-90%', 9), Dual('0-100%', 10)))))))))), (PartnerID, (TEXT, ASC)))

Expressions:

1) =Count(DISTINCT PartnerID)

2) =Sum(Sales)

3) =Column(2)/Column(1)

View solution in original post

9 Replies
sunny_talwar

Would you be able to share a sample so that we can test/play around with what you have to get you the desired output?

Not applicable
Author

Hi Sunny,

PFA the attached QVW with sample inline load data. the chart in this QVW(chart 1) needs to be summarized like the way I have done in excel screenshot below (chart 2) based on 'cumulative bucket' column.

while I was preparing this sample chart, I came across another unusual thing- if I place the 'cumulative bucket' just next to 'cumulative %' column it does not give the correct output, but if place a column away it works fine.can you also help me understand this too(this is anyhow not important its just a curious observation ).

Chart 1

Chart 2

   

cumulative bucketCount of PartnerIDSum of SalesAPP(column2/column1)
10%38428
100%210653
20%19393
30%19292
40%15757
70%2314157
90%421353.25
Grand Total1495968.5
sunny_talwar

Like this?

Capture.PNG

I hope you have QV12, because you will need to use this -> The sortable Aggr function is finally here!

Dimension:

=Aggr(

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.10, Dual('0-10%', 1),

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.20, Dual('0-20%', 2),

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.30, Dual('0-30%', 3),

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.40, Dual('0-40%', 4),

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.50, Dual('0-50%', 5),

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.60, Dual('0-60%', 6),

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.70, Dual('0-70%', 7),

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.80, Dual('0-80%', 8),

If(RangeSum(Above(TOTAL Sum(Sales)/Sum(TOTAL(Sales)), 0, RowNo(TOTAL))) <= 0.90, Dual('0-90%', 9), Dual('0-100%', 10)))))))))), (PartnerID, (TEXT, ASC)))

Expressions:

1) =Count(DISTINCT PartnerID)

2) =Sum(Sales)

3) =Column(2)/Column(1)

Not applicable
Author

Thanks Sunny, it works perfectly.

but there is one problem, when we sort the data in 'descending' sales in chart 1, then our summarized calculated dimension does not match. any suggestion on how we can alter the dimension expression to get the chart 2 as per descending sales.sorry I missed this crucial part when I first drafted.

thanks in advance for your help/suggestion.

Regards,

Avishek

sunny_talwar

Yes and that is because these are two different charts. You will have to decide what sorting do you want here.

Not applicable
Author

HI Sunny,

I wanted to get the same out put but with 'sales' sorted Descending.

Thanks in advance for your kind advice/help in order to achieve this.

Avishek

Not applicable
Author

Hello Sunny,

Were you able to tweak the expression for Chart 2? (sorted Descending SALES)

Thanks,

Avishek

sunny_talwar

AFAIK this cannot be done. May be someone else might be able to offer some better advice

Not applicable
Author

Thank you Sunny, you still managed to solve it to good extent.:)

I think the requirement is little unrealistic.

let me take few more views before I give up on this.