topic Sum Values where another field is not null... in QlikView

Sum Values where another field is not null...

Thu, 16 Jun 2011 09:07:07 GMT

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

Sum Values where another field is not null...

Thu, 16 Jun 2011 09:16:32 GMT

Try this:

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

Sum Values where another field is not null...

matt_crowther — Thu, 16 Jun 2011 09:19:43 GMT

=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

Sum Values where another field is not null...

Thu, 16 Jun 2011 09:20:51 GMT

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

Sum Values where another field is not null...

ToniKautto — Thu, 16 Jun 2011 09:21:23 GMT

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.

Sum Values where another field is not null...

Thu, 16 Jun 2011 09:28:46 GMT

Try this then:

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

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

Sum Values where another field is not null...

Thu, 16 Jun 2011 09:46:47 GMT

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.

Sum Values where another field is not null...

matt_crowther — Thu, 16 Jun 2011 09:55:37 GMT

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

Sum Values where another field is not null...

pat_agen — Thu, 16 Jun 2011 09:58:19 GMT

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

Sum Values where another field is not null...

Thu, 16 Jun 2011 10:23:53 GMT

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)