Sum Values where another field is not null...

[#1]

Hi

I'm pretty new to QlikView and have hit a brick wall, normally I have found something on the community to answers my question, but not this time.  What I need is a sum of Charge Out Rates in the selected year, where the person has a time budget.  Sample of data is shown below.  For example if I select year 2011/12 I would want to return £380 (Name1 + Name3).

I have tried:

if(IsNull(TIME_BUDGET)=0,(sum({$<CO_Fin_Year = Period_Fin_Year>} charge_out_rate)),0)

Any help really appreciated.

Name	Charge_Out_Rate	CO_Fin_Year	TIME_BUDGET
Name1	185	2009/10	100
Name1	185	2010/11	100
Name1	195	2011/12	100
Name2	0	2008/09
Name2	0	2009/10
Name2	0	2010/11
Name2	0	2011/12
Name2	140	1996/97
Name2	140	2007/08
Name3	170	2009/10	50
Name3	175	2010/11	50
Name3	185	2011/12	50

[#2]

Try this:

if(IsNull(TIME_BUDGET)=0,(sum({$<CO_Fin_Year = {'=Max(Period_Fin_Year)'}>} charge_out_rate)),0)

[matt_crowther]

=if(sum(total <Name>Budget)>0,sum(Charge),0)

Works for me.

Sure there will be alternate ways to go about it as well.

Hope that helps,

Matt - Visual Analytics Ltd

[#4]

Hi Erika

Thanks for the idea, the

sum({$<CO_Fin_Year = Period_Fin_Year>} charge_out_rate)

part of the code is working ok as I use it else where and I want it to change if the user changes the year.  The part I am struggling with is to only sum the charge out rate if the person has a budget.

Jon

[ToniKautto]

There is no need to use IsNull() = 0, since IsNull itself returns the boolean required in the if statement.

Please elaborate a bit on what object you are using when preenting the data, and in what way the result is incorrect. If possible please attach a QVW file to make evaluation of your dilemma a bit quicker.

[#6]

Try this then:

if(IsNull(TIME_BUDGET),0,Expression))

or

if(TIME_BUDGET<>0,Expression,0))

[#7]

Thanks all for your quick responses.

Toni, you are correct it would help if I elaborated further.  Currently I am adding this to a text object to get a Summary across all people within a department.

If I use the code suggested by Matt and put it in a Straight table with the Name as the dimension it works ok as it is evaluating the Time_Budget for each name.  When I remove the dimension it then fails because sum of Time_Budget for all Names together is by definition > 0.

Is there a way to get QlikView to evaluate the the formaula per name and then return a sum of the results?

Hope that makes sense.

[matt_crowther]

May not be a solution but the formula I suggested does work if placed in a text object; so in that context it doesn't require any dimensions.

All the best,

Matt - Visual Analytics Ltd

[pat_agen]

hi,

going by your initial dataset a user has the time_budget against every record for which it has a charge_out_rate and and financial year. If that is the case the following should get you what you require:

sum({$<CO_Fin_Year ={Period_Fin_Year}>}if(IsNull(TIME_BUDGET)=0, Charge_Out_Rate,0) )

you are better doing a sum(if()) rather than an if(sum()).

[#10 · Accepted solution]

Hi All

Thanks for all your input, managed to get to the bottom of it in the end as follows:

=sum({$<CO_Fin_Year = Period_Fin_Year, TIME_BUDGET = {'>=0'} >} charge_out_rate)