Here is your Ans.
Have a look at the attched Document.
example.qvw 128.8 K
Well, it's crappy and doesn't scale well, but it works...
=Sum(If(month = month_forecast, forecast))
Here, the problem is that the expression "forecast" is translated by QV as "Only(forecast)" or "return me the forecast if there is ONLY ONE VALUE FOR DIMENSION COMBINATION. Actually you have 12, so you have to select only one of them. The one matching the month. I'm doing it with an If() function.
The Sum is just to have a total value. You could use Only() instead.
Whoops... I didn't read the topic until the end.
Well, in the last Qvw file you have this expression:
=sum(if(num([HEL_DATA_Datum > MONTH])= SAL_PLANDATEN_Budget,SAL_PLANDATEN_Budget))
This is wrong.
num([HEL_DATA_Datum > MONTH]) is a month (1-12).
SAL_PLANDATEN_Budget is a budget (dollars).
Using the if can't extract you anything.
You have to compare apples to apples .
Probably that line should read:
=sum(if([HEL_DATA_Datum > MONTH]=Num#(SAL_PLANDATEN_Monat), SAL_PLANDATEN_Budget))
If I understand the field names correctly.
Now, for the "wrong" values. It depends upon the many join you pass from table PLANDATEN (where the Budget is) to table HELIOS (where Month is). AFAICT, it makes no sense.
You should first understand what you're doing.
Probably, as a quick solution, you can do:
=Only(if([HEL_DATA_Datum > MONTH]=Num#(SAL_PLANDATEN_Monat), SAL_PLANDATEN_Budget))
that's it! It was because of the num function. Many many thanks.
However, I did not quite understand the difference between num and num #. The help
in Qlikview is confusing. What I understand is that there probably is a difference between Intepretation and
formatting. Can someone explain to me in the very simple example of the difference. Thanks again
The difference between Num and Num# is the difference between Formatting and Interpreting.
In the first case you have a value (numeric, usually - in QV this means a double) and you want to FORMAT the result. This means "value to string".
Interpreting means you have a string (a Text) and you want to convert it to a value (date, number, etc.).
So they do quite the opposite: Num() casts a number to string with formatting options, Num#() casts a string to a number using the provided options for string interpretation.
In the document provided by jup, if you create a table box with the month field in it, you can see the value is stored in QlikView as a string (this is basically wrong, AFAICT, and means an edit in the load script could be helpfull) from the alignment in the cell: the month is aligned to the left, as a text, instead on the right, as a number.
So, if you want to compare the month text to a number, you must convert it first to number using Num# (or, better, change the load script adding a conversion at load time). The same function Num# would do the job.