Using Raw Data: No Value referenced with symbol

[#1]

Hi,

I have got some raw data in Excel looking like:

Data1	Data2
1	-
-	8
11	-
7	8
-	1

So if there is no value, I do have a "-" in the cell.

However, I would like to leave the data untouched.

How can I define that these cells are skipped when calculating stuff like averages or simple summation in Qlikview?

Many thanks in advance

[sunny_talwar]

May be this:

Avg({<Data1 -= {'-'}>}Data1)

Note the small negative sign in red before the equal sign

[swuehl]

So these records show a character '-'? Or is this your representation of NULL?

You probably won't need to do anything.

Sum() will only sum up numeric values. Also Avg() should only operate on the numeric values.

[#4]

Thanks, seems to work.

Can I define this overall in the script?

Did some x+z as y loading in the script, which doesn´t work with the "-" values...

[#5]

See above, that doesn´t work with predefined formulas.

[sunny_talwar]

Not sure I understand, can you provide a sample of what you are doing?

[swuehl]

Try

Rangesum(x,z) as y

[#8]

Companyname	Data1	Data2
A	1	-
A	-	8
B	11	-
B	7	8
C	-	1
C	7	-

So in the loadscript I have defined

Rangesum(Data1,Data2) as Data3

Depending on the chosen Company I want to illustrate the result in a table.

Using SUM({<Companyname>}[Data3]) does not get me the right result.

What is the right function?

[sunny_talwar]

What is the number you are expecting to see from the above sample?

[swuehl]

Is this what you get when executing the LOAD?

Companyname	Data1	Data2	Data3
A	-	8	8
A	1	-	1
B	7	8	15
B	11	-	11
C	-	1	1
C	7	-	7

This is when I use Companyname as dimension in a chart and

SUM({<Companyname>}[Data3])

as expression:

Companyname	SUM({<Companyname>}[Data3])
	43
A	9
B	26
C	8

Seems ok to me, or please advise what you expect instead.

Also, as I mentioned above, Sum() and Avg() functions don't seem to worry about your '-' values:

Companyname	SUM({<Companyname>}[Data3])	Sum(Data1)	Avg(Data1)
	43	26	6,5
A	9	1	1
B	26	18	9
C	8	7	7