14 Replies Latest reply: Aug 25, 2015 11:02 AM by Jeff Robertz RSS

    Sum with multiple if/vlookups

    Jeff Robertz

      I am blocked with what seems an easy issue.

       

      I load an Excel file that combine hours or amounts for several cost items.

      I agree this is a strange set-up but that is how the client structured its data and have worked around all the way down except for a KPI and a bar chart formula.

      The result look like this after loading my it in Qlikview and computing the TotalFigure, MonthlyCost, TotalCost, MonthlyPrice, TotalPrice

                

      Cat. ExpenditureCatStart DateEnd DateMonthYearNumberOfMonthMonthlyFigureTotalFigureMonthlyCostTotalCostMonthlyPriceTotalPrice
      1. PersonnelEngineer1/01/201631/12/20161/01/20161250.00600.006,501.8378,021.936,989.4683,873.58
      1. PersonnelExpert1/01/201631/12/20161/02/20161250.00600.006,501.8378,021.936,989.4683,873.58
      2. Direct OverheadEngineer1/01/201631/12/20161/01/20161210.00120.001,300.3715,604.391,397.8916,774.72
      2. Direct OverheadExpert1/01/201631/12/20161/02/20161210.00120.001,300.3715,604.391,397.8916,774.72
      3. Travel15/01/201716/01/201715/01/201716,000.006,000.006,000.006,000.006,579.006,579.00
      3. Travel23/03/201725/03/201723/03/2017119,200.0019,200.0019,200.0019,200.0021,052.8021,052.80
      5. Equipment1/04/201616/03/20171/02/201712833.3310,000.00833.3310,000.00913.7510,965.00
      5. Equipment1/04/201616/03/20171/03/201712833.3310,000.00833.3310,000.00913.7510,965.00
      6. Dissemination/Communication 1/10/201631/12/20161/11/20163333.331,000.00333.331,000.00365.501,096.50
      6. Dissemination/Communication 1/10/201631/12/20161/12/20163333.331,000.00333.331,000.00365.501,096.50
      7. Miscellaneous1/05/201631/07/20161/06/201631,000.003,000.001,000.003,000.001,096.503,289.50
      7. Miscellaneous1/05/201631/07/20161/07/201631,000.003,000.001,000.003,000.001,096.503,289.50
      8. Risk 1/01/201631/12/20161/11/2016121,000.0012,000.001,000.0012,000.001,096.5013,158.00
      8. Risk 1/01/201631/12/20161/12/2016121,000.0012,000.001,000.0012,000.001,096.5013,158.00
      4. External Services15/04/201628/04/201615/04/2016110,000.0010,000.0010,000.0010,000.0010,965.0010,965.00
      4. External Services1/07/20184/07/20181/07/201812,400.002,400.002,400.002,400.002,631.602,631.60

       

      While I get want I want in a table, I can't manage to compute the sum of monthly cost in a KPI box or even in a bar chart.

      The issue is that if Cat. Expenditure is 1. Personnal or 2. Direct Overhead, I need to apply an hourly rate [Hourly rate] from another table - see attached file - to transform the MonthlyFigure (which represents hours for those items) into a money amount. I need here to account for the Cat profile to select the right rate comprised in the Rate sheet.

      But for all other cost items (3. Travel, 5. Equipment, ...), the MonthlyFigure is already an amount so I just need to sum up the amount.

      I tried this formula but it does not work:


      if([Cat. Expenditure]='1. Personnel (cat. PSS form 1)',sum(MonthlyFigure*[Hourly rate])

      +

      if([Cat. Expenditure]='2. Direct Overhead  (cat. PSS form 9)',sum(MonthlyFigure*[Hourly rate])

      +

      sum(MonthlyFigure)))

       

      Same issue in a bar chart. I do not get the right value, unless I include Cat. Expenditure as a dimension.

      Can you help me solve this issue?

      Million thanks Community guys :-)

        • Re: Sum with multiple if/vlookups
          Stefan Wühl

          Try embedding the if() within the sum() aggregation function

           

          sum( if( Match( [Cat. Expenditure],'1. Personnel (cat. PSS form 1)','2. Direct Overhead  (cat. PSS form 9)'), MonthlyFigure*[Hourly rate]))

          +

          sum(MonthlyFigure)))

          • Re: Sum with multiple if/vlookups
            Sasidhar Parupudi

            if([Cat. Expenditure]='1. Personnel (cat. PSS form 1)',sum({<[Cat. Expenditure]={'1. Personnel (cat. PSS form 1)'}>}MonthlyFigure*[Hourly rate])

             

            +

             

            if([Cat. Expenditure]='2. Direct Overhead  (cat. PSS form 9)',sum({<[Cat. Expenditure]={'2. Direct Overhead  (cat. PSS form 9)'}>}MonthlyFigure*[Hourly rate])

             

            +

             

            sum(MonthlyFigure)))

            • Re: Sum with multiple if/vlookups
              Jonathan Dienst

              Have you considered applying the rates during the load process. First load the rate table with a mapping load, then load the detail.Something like:

               

                   MapRates:

                   Mapping LOAD ....

                   FROM Rates....;

               

                   Detail:

                   LOAD ...

                        If(WildMatch([Cat. Expenditure], '1.*', '2.*'), ApplyMap('MapRates', [Cat. Expenditure]), 1) * MonthlyFigure) As MonthlyCost,

                        ...

               

              Then there is no need for complex expressions in the front end.

                • Re: Sum with multiple if/vlookups
                  Jeff Robertz

                  Hi Jonathan,

                   

                  I very much like the approach proposed.

                  My only issue is that this comes in combination with calculation that distribute an amount over a variable amount of month.

                  I tried to make it work in my script but this does not work, any chance you can help me fix this?

                  Here is the script:

                   

                  MapRates:

                  LOAD Cat,

                    //[Basic hourly rate],

                      //[Direct overhead],

                      [Hourly rate]

                  FROM

                  ...

                   

                   

                  tabAmount: 

                  LOAD

                    [PROJECT NAME :],

                       [DEPARTMENT :],

                       [Cat. Expenditure],

                       Title,

                       Cat,

                       Activity,

                       WP as ESSP_WP,

                       [Start Date],

                       MonthName([Start Date]) as MonthStart,

                       [End Date],

                       Monthname([End Date]) as MonthEnd,

                       Days,

                       (Year([End Date])*12 + Month([End Date]))-(Year([Start Date])*12 + Month([Start Date]))+1 as NumberOfMonth,

                       [Number/amount] as TotalFigure,

                       RecNo() as ID, 

                      [Number/amount]/((Month([End Date])-Month([Start Date]))+12*(Year([End Date])-Year([Start Date]))+1) as MonthlyFigure,

                      If(WildMatch([Cat. Expenditure], '1.*', '2.*')>0, ApplyMap('MapRates', Cat), 1) * ([Number/amount]/((Month([End Date])-Month([Start Date]))+12*(Year([End Date])-Year([Start Date]))+1)) As MonthlyCost,

                      AddMonths([Start Date],IterNo()-1) as MonthYear

                  FROM

                  ...

                  While AddMonths([Start Date],IterNo()-1)<[End Date];

                   

                   

                  tabMonths:  

                  LOAD AddMonths(MinMonth,IterNo()-1) as MonthYear 

                  While AddMonths(MinMonth,IterNo()-1)<=Today(); 

                  LOAD Min(MonthYear) as MinMonth 

                  Resident tabAmount;

                    • Re: Sum with multiple if/vlookups
                      Jonathan Dienst

                      The LOAD below should read:

                       

                      MapRates:

                      Mapping LOAD ....

                       

                      Jeff Robertz wrote:

                      I tried to make it work in my script but this does not work, any chance you can help me fix this?

                      Here is the script:

                       

                      MapRates:

                      LOAD Cat,

                        //[Basic hourly rate],

                          //[Direct overhead],

                          [Hourly rate]

                      FROM

                      ...

                  • Re: Sum with multiple if/vlookups
                    Jeff Robertz

                    Hi Swuehl,

                     

                    I am trying to share the file with you but this chat seems it does not allow me doing so.

                    Do you know how to do?

                    Or alternatively, I could send you the file in direct and post it back to the community once solved.

                     

                    What do you think?

                     

                    Jeff