Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
agaetisproject
New Contributor III

Sum with multiple if/vlookups

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

14 Replies
MVP
MVP

Re: Sum with multiple if/vlookups

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

sasiparupudi1
Honored Contributor III

Re: Sum with multiple if/vlookups

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

MVP
MVP

Re: Sum with multiple if/vlookups

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
agaetisproject
New Contributor III

Re: Sum with multiple if/vlookups

I am sorry, this does not work Swuehl. I cannot get a result with this.

Looking at the expression, you do not have a sum prior MonthlyFigure*[HOurly rate] while you have one in front of MonthlyFigure, any reason why?

agaetisproject
New Contributor III

Re: Sum with multiple if/vlookups

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;

MVP
MVP

Re: Sum with multiple if/vlookups

There is a sum() for aggregation, I just embedded the if() statement in the sum():

sum(

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

)


I noticed that I left over some closing brackets at the very end of the expression which shouldn't be there, I think you have noticed and removed them?

agaetisproject
New Contributor III

Re: Sum with multiple if/vlookups

What I don't get there is how it translate the fact that I need the following:

adding

MonthlyFigure*[Hourly rate] in case of personnel cost

to

Travel or other type of costs if not personnel

If I analyse your proposed expression, I get the following 'excuse my lack of scripting science :-)

we sum

if personnel cost (1.* or 2.*), we multiply by hours

if non personnel cost, we need to still consider the personnel costs and add the travel,... cost

So, it not one or the other, it is both.

And when I try, I don't get values.


Could your genius help me out of here?

MVP
MVP

Re: Sum with multiple if/vlookups

Ok, the case for 3 and above could be included into the THEN branch of the IF() statement:

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

agaetisproject
New Contributor III

Re: Sum with multiple if/vlookups

It still does not work Swuehl.

Should I add an aggr in there so QlikView understand that he needs to sum up across all Cat. Expense?

One thing to precise is that the [Hourly rate] varies per Cat (not Cat. Expense).

Community Browser