8 Replies Latest reply: Oct 6, 2015 12:09 PM by Digvijay Singh RSS

    Totals using set analyais

    Andrew Thomas

      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

       

       

       

       

       

        • Re: Totals using set analyais
          Ruben Marin

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


          • Re: Totals using set analyais
            Digvijay Singh

            Something like this -

            =Sum(aggr(sum(TOTAL {$<IG_STATUS={'Open'}>} PlannedCostsLCV0),Activity)) - Sum(aggr(sum(TOTAL {$<IG_STATUS={'Open'}>} PlannedCostsLCV1),Activity))

              • Re: Totals using set analyais
                Andrew Thomas

                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.2106,439,351.2
                843703  Direct Acty Allocn Manf Support SVC Hrs172,804.0172,804.0
                843710  PS Mechancial Engineering/Design62,644,757.962,644,757.9
                843711  PS Controls Engineering/Design3,055,767.53,055,767.5
                843712  PS Controls Programming52,415.052,415.0
                843713  PS Documentation3,143,750.23,143,750.2
                843714  PS Service Field support5,399,332.25,399,332.2
                843715  PS Project Management30,291,165.230,291,165.2
                843716  PS FAT/Install assist.386,222.8386,222.8
                843721  Applications Engineering219,016.7219,016.7
                843722  PS Procurement - Buyer/Planners449,944.1449,944.1
                843723  PS Contract Administration86,482.386,482.3
                843724  PS Quality521,807.2521,807.2
                843743  Direct activity allocn of Selling OS hrs15,886.215,886.2
              • Re: Totals using set analyais
                William Davidson

                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