Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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)
Try this:
if(IsNull(TIME_BUDGET)=0,(sum({$<CO_Fin_Year = {'=Max(Period_Fin_Year)'}>} charge_out_rate)),0)
=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
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
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.
Try this then:
if(IsNull(TIME_BUDGET),0,Expression))
or
if(TIME_BUDGET<>0,Expression,0))
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.
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
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()).
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)