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

[#2]

I do have a further question:

So when I have selected Company A and its sector is Automotive I want to use one formula, if it is any other sector I want to use another formula.

Companyname	Sector	Year	Data1	Data2
A	Automotive	2016	1	-
A	Automotive	2015	-	8
B	Financial Industry	2016	11	-
B	Financial Industry	2015	7	8
C	Public Sector	2016	-	1
C	Public Sector	2015	7	-

Unfortunately this does not work. So if it is any other sector it does work, if it is automotive it does not:

=if(Sector='Automotive', num(only({<Companyname,Year={$(vYear1)}>}[Data3]),'#.#%'), num(only({<Companyname,Year={$(vYear1)}>}[Data4]),'#.#%'))

[sunny_talwar]

Try this:

Num(RangeSum(Only({<Companyname, Year={$(vYear1)},Sector = {'Automotive'}>} [Data3]),                                                  Only({<Companyname, Year={$(vYear1)}, Sector -= {'Automotive'}>} [Data4])),'#.#%')

[#4]

That works almost perfectly. I just wann add an or operator to the sector but it seems like I do not get the syntax right, what is the mistake?

Num(RangeSum(Only({<Companyname, Year={$(vYear1)},<Sector = {'Automotive'}+<Sector = {'Financial Industry'}>} [Data3]), Only({<Companyname, Year={$(vYear1)},<Sector -= {'Automotive'}+<Sector -= {'Financial Industry'}>} [Data4])),'#.#%')

[swuehl]

Num(

RangeSum(

Only({<Companyname, Year={$(vYear1)}, Sector = {'Automotive','Financial Industry'}>} [Data3]), Only({<Companyname, Year={$(vYear1)}, Sector -= {'Automotive','Financial Industry'}>} [Data4])

)

,'#.#%')

[#6]

Many thanks, seems to work. I have created a variable called vCalculation. And two text objects which set a value for this variable. I want one text box to switch to average calculations and one as the above per year. What is the value to be set for the variable in the text box and how can I fit this in the above expression?

Num(

vCalculation,Sector = {'Automotive','Financial Industry'}>} [Data3]), Only({<Companyname, Year={$(vYear1)}, Sector -= {'Automotive','Financial Industry'}>} [Data4])

)

,'#.#%')

So like one textbox sets for the variable:

RangeSum(Only({<Companyname, Year={$(vYear1)}

The other textbox sets for the variable:

RangeAvg(Only(

Hope it is clear what I want. Basically replacing the desired function...

[sunny_talwar]

May be something like this:

Num(RangeAvg($(vCalculation)))

and

Num(RangeSum($(vCalculation)))

Where vCalculations = Only({<Companyname, Year={$(vYear1)}, Sector = {'Automotive','Financial Industry'}>} [Data3]), Only({<Companyname, Year={$(vYear1)}, Sector -= {'Automotive','Financial Industry'}>} [Data4])

[#8]

Well basically the other way around. What you have defined as the Variable is what I want to be fixed.

I wann change between RangeSum and RangeAvg when clicking on the different textboxes which set values for the variable (e.g. "RangeSum").

[sunny_talwar]

Then have vCalculations as a inputbox where you can enter either RangeSum or RangeAvg and then this:

Num($(vCalculations)(

Only({<Companyname, Year={$(vYear1)}, Sector = {'Automotive','Financial Industry'}>} [Data3]), Only({<Companyname, Year={$(vYear1)}, Sector -= {'Automotive','Financial Industry'}>} [Data4])))

[#10]

I have another issue with such an expression.

Using the following one in a bar chart with year as dimension it always adds up both expressions.

Num(RangeSum((sum({<Sector -= {'Automotive','Financial Industry'}>}[Data1])/Above(sum({<Year,Sector -= {'Automotive','Financial Industry'}>}[Data1]))-1)

/(sum({<Sector -= {'Automotive','Financial Industry'}>}[Data2])/Above(sum({<Year,Sector -= {'Automotive','Financial Industry'}>}[Data2]))-1),

(sum({<Sector = {'Automotive','Financial Industry'}>}[Data1])/Above(sum({<Year,Sector = {'Automotive','Financial Industry'}>}[Data1]))-1)

/(sum({<Sector = {'Automotive','Financial Industry'}>}[Data3])/Above(sum({<Year,Sector = {'Automotive','Financial Industry'}>}[Data3]))-1)),'#.##0,0%')

What I want is that it uses one of the formulas depending on the Selection of Sector.