Pivot Table Problem (not related data)

[#1]

Hi everybody,

I have a problem to get the matching objects for the budget (data is not related by the date range):

Could somebody help me please to get the correct values in the column "Buget" for

- 01/2010 --> forecast-value "20" in column "Budget"

- 02/2010 --> forecast-value "231" in column "Budget"

Attached you can find an example QVW-File and the 3 example-XLS-Sheets.

Thanks a lot.

Regards,

jup

...

[kaushiknsolanki]

Hi,

     I have found whats the problem is, please give me some time. I dont have my laptop with me now.

     I will get back to you soon

Regards,

Kaushik Solanki

[#3]

Hi Kaushik,

that would be great. Thank you very much !

Best regards,
Onur

[#4]

Well, it's crappy and doesn't scale well, but it works...

Instead of:

=forecast

use:

=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.

Good luck!

[#5]

Hi chiesa,

this is exactly what Kaushik wrote in his post/his example[1].qvw file.

But take a look at my example_Shrinked_FORUM.qvw file. In this file it doesn't work 😞

thanks,

jup

[#6 · Accepted solution]

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

[#7]

Hello Chies080,

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

Best regards,

[#8]

Hey chiesa!

You did it! Great job - thanks very much and many thanks also to Kaushik 🙂

You are great!

jup

[#9]

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.

Good luck!

[#10]

Hi chiesa80,

that explains everything. Thank you!

Best regards,