Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 BI | 500 |
Qlikview | 2500 |
Tableau | 2500 |
Qlik Sense | 2750 |
Output Should be :
Thanks in Advance!
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.
@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).