Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
paulwalker
Creator II
Creator II

Distribute value in Pie Chart

Hi Community,

Not sure my query complex or not - let say I have to show below values in Pie chart

Dim: Category

Exp: SUM(Amount)/SUM(Total AMOUT)

My Scenario, Qlik Sense should not be show in Pie chart - but that value need to be split to Power BI (70%) and Qlikview (30%)

Qlik Sense 70% = 1925 and Qlik Sense 30% = 825

Category Amount 
Power BI500
Qlikview2500
Tableau2500
Qlik Sense2750

 

Output Should be : 

paulwalker_0-1630329567753.png

Thanks in Advance!

 

Labels (1)
2 Replies
stevejoyce
Specialist II
Specialist II

Front-end expression, Numerator:

Sum(Amount) + (Sum(Total {<Category={'Qlik Sense'}>} Amount) * Sum({<Category={'Power BI', 'Qlikview'}>} 1) * alt(pick(match(Category, 'Power BI', 'Qlikview'), .7, .3),0))

 

You could do this in the load script but your data model might be more complicated then just these 2 columns.  You could add an "AdjustedAmount": 1) have a mapping table with Category, QlikSenseFactor 2) Sum QlikSense Amount and join it to your data table (in this simple case, no associations needed, as you would want it to reflect all rows, but if you had other dimensions like date you can aggregate and join by that).  3) Add column Amount + (QlikSenseTotal * applymap('map_qliksensefactor', Category, 0)) as AdjustedAmount.  4) your chart would use AdjustedAmount.

Or
MVP
MVP

@stevejoyce  beat me to the punch with essentially the same solution and the same recommendation to do it script-side if possible. One added note - you will need to multiply the entire expression by if(Category = 'Qlik Sense',0,1) to avoid Qlik Sense being counted twice (once in its own category and once being split into the other categories).