Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Im trying to create a pie chart and need to total the dimension that I want to use
I have the following formula
(
sum(TOTAL {$<IG_STATUS={'Open'}>} PlannedCostsLCV0 ) - sum(TOTAL {$<IG_STATUS={'Open'}>} PlannedCostsLCV1 ))
This gives me the total of ALL PlannedCostsLCV0
I have a dimension called Activity and I want the formula to total PlannedCostsLCV for just the Activity dimension
Hi aNdrew, maybe using a Dimension total por PlannedCostsLCV0:
(
sum(TOTAL <Activity> {$<IG_STATUS={'Open'}>} PlannedCostsLCV0 ) - sum(TOTAL {$<IG_STATUS={'Open'}>} PlannedCostsLCV1 ))
If the pie dimension is already activity you can remove the TOTAL of PlannedCostsLCV0:
(
sum({$<IG_STATUS={'Open'}>} PlannedCostsLCV0 ) - sum(TOTAL {$<IG_STATUS={'Open'}>} PlannedCostsLCV1 ))
If you also want PlannedCostsLCV1 per activity you can apply the same options for the second part of the expression:
(
sum({$<IG_STATUS={'Open'}>} PlannedCostsLCV0 ) - sum({$<IG_STATUS={'Open'}>} PlannedCostsLCV1 ))
Something like this -
=Sum(aggr(sum(TOTAL {$<IG_STATUS={'Open'}>} PlannedCostsLCV0),Activity)) - Sum(aggr(sum(TOTAL {$<IG_STATUS={'Open'}>} PlannedCostsLCV1),Activity))
Thanks
That gave me the total of PlannedCostLCV0 - PlannedCostsLCV1 for each activity type, But what I need is the total against each activity type (106,439,351.2)
=if(left(Activity,4) = '8437' or left(Activity,6) = '843802' or left(Activity,6) = '843803', Activity) | % Variance (Plan 0 - Plan 1) | % Variance (Plan 0 - Plan 1) |
106,439,351.2 | 106,439,351.2 | |
843703 Direct Acty Allocn Manf Support SVC Hrs | 172,804.0 | 172,804.0 |
843710 PS Mechancial Engineering/Design | 62,644,757.9 | 62,644,757.9 |
843711 PS Controls Engineering/Design | 3,055,767.5 | 3,055,767.5 |
843712 PS Controls Programming | 52,415.0 | 52,415.0 |
843713 PS Documentation | 3,143,750.2 | 3,143,750.2 |
843714 PS Service Field support | 5,399,332.2 | 5,399,332.2 |
843715 PS Project Management | 30,291,165.2 | 30,291,165.2 |
843716 PS FAT/Install assist. | 386,222.8 | 386,222.8 |
843721 Applications Engineering | 219,016.7 | 219,016.7 |
843722 PS Procurement - Buyer/Planners | 449,944.1 | 449,944.1 |
843723 PS Contract Administration | 86,482.3 | 86,482.3 |
843724 PS Quality | 521,807.2 | 521,807.2 |
843743 Direct activity allocn of Selling OS hrs | 15,886.2 | 15,886.2 |
Can you share small sample with actual fields and output, not getting exact need..
Hi
I'm trying to create a pie chart that gives the percentage variance by activity type. I have activity type as a dimension and then in my expressions i'm trying to do the calc Planned costsV0 - Planned Costs V1 / Planned Cost V0 to give a percentage. What I'm currently getting though is a value that is the percentage per activity type and not a percentage of the total value of planned costs V0
So in the data example I sent I need to show :
% of Activity 843724 = 521,807 / 106,439,351 = 0.0049
At the moment Im just getting 100% as the formula is calculating 521,807 / 521,807
Hi Andrew,
Just wanted to share the Qlik Set Analysis Wizard tool. It's a life saver.
http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=
Hopefully it helps.
Will
Hi Andrew, can you try this?:
(sum({$<IG_STATUS={'Open'}>} PlannedCostsLCV0 ) - sum({$<IG_STATUS={'Open'}>} PlannedCostsLCV1 ))
/ sum(TOTAL {$<IG_STATUS={'Open'}>} PlannedCostsLCV0 )
You can also use:
sum({$<IG_STATUS={'Open'}>} PlannedCostsLCV0 ) - sum({$<IG_STATUS={'Open'}>} PlannedCostsLCV1 )
and check 'relative'
I tried to create sample data, see if we can link the actual need with this sample.