Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table that I have imported into QV containing budget information. The table has the following fields:
Objective
FY_Budget
Numerical_Forecast
The accountants have this thing they are fixated on called the Forecast_Outturn which isn't included in the budget table. It's calculated on the fly and the rule is:
If (Numerical_Forecast is NULL)
Forecast_Outturn = FY_Budget
Else
Forecast_Outturn = Numerical_Forecast
End If
I want to create a simple chart that shows the FY_Budget against the Forecast_Outturn for any given Objective Code. Can anyone suggest the best way to calculate the Forecast_Outturn value (i.e. should I do it in the load script, as an expression etc.) and make it available to my QlikView workbook?
Thanks in advance,
markp3rry.
You need to use a group by clause to allow the use of an aggregation function like sum.
You could do the sum on the DB server, using the SQL syntax of this system. Maybe you need to ask someone from your IT to help you with that (it will look probably (almost) identical to below statements):
Or try
TMPbudgets:
SQL SELECT * FROM BUDGET;
budgets:
LOAD
OBJECTIVE,
sum(FY_BUDGET) as FY_BDUGET,
sum(NUMERICAL_FORECAST) as NUMERICAL_FORECAST,
IF( SUM(NUMERICAL_FORECAST) = 0, sum(FY_BUDGET), sum(NUMERICAL_FORECAST)) AS Forecast_Outturn
resident TMPbudgets group by OBJECTIVE;
drop table TMPbudgets;
I would create the Numerical_Forecast as new field in your script:
LOAD
Objective,
FY_Budget,
Numerical_Forecast,
if(isnull(Numerical_Forecast),FY_Budget,Numerical_Forecast) as Forecast_Outturn
FROM Table;
Then create e.g a straight table chart with dimension Objective and expression
=FY_Budget / Forecast_Outturn
(or whatever you want to see)
Regards,
Stefan
Thanks for the help Stefan - really appreciate it. Just checked the database and the Numerical_Forecast can either be NULL or 0 - how can I modify the LOAD script to accomodate this?
Try
if(isnull(Numerical_Forecast) or Numerical_Forecast=0,FY_Budget,Numerical_Forecast) as Forecast_Outturn
and if Numercial_Forecast can be NULL, 0 or blank:
if(len(trim(purgechar(Numerical_Forecast,'0')))=0,FY_Budget,Numerical_Forecast) as Forecast_Outturn
Thanks again! If each Objective has multiple rows (made up of a number of different sub budgets) then how would I SUM each set of values for each Objective in the expression?
I tried
budgets:
LOAD OBJECTIVE,
FY_BUDGET,
NUMERICAL_FORECAST,
IF(ISNULL(SUM(NUMERICAL_FORECAST)) OR SUM(NUMERICAL_FORECAST) = 0, FY_BUDGET, NUMERICAL_FORECAST) AS Forecast_Outturn;
SQL SELECT * FROM BUDGET;
but it throws an error
You need to use a group by clause to allow the use of an aggregation function like sum.
You could do the sum on the DB server, using the SQL syntax of this system. Maybe you need to ask someone from your IT to help you with that (it will look probably (almost) identical to below statements):
Or try
TMPbudgets:
SQL SELECT * FROM BUDGET;
budgets:
LOAD
OBJECTIVE,
sum(FY_BUDGET) as FY_BDUGET,
sum(NUMERICAL_FORECAST) as NUMERICAL_FORECAST,
IF( SUM(NUMERICAL_FORECAST) = 0, sum(FY_BUDGET), sum(NUMERICAL_FORECAST)) AS Forecast_Outturn
resident TMPbudgets group by OBJECTIVE;
drop table TMPbudgets;
Hi,
try this
budget:
Load
objective,
fy_budget,
numeric_forecast,
if (isnull(fy_budget)=-1 or len(fy_budget)=0,fy_budget,numeric_forecast) as forecast_outturn
from
xyz.qvd;
thanks®ards
rohit
Thanks for your help Stefan - really appreciate it.