I'm new to Qlikview and I'm trying to create a score for both the unit and an average score for the buyer based on the value of a number of fields.
num(UNIT.M_M / 1000) + UNIT.AGE + (UNIT.FIX*10) + (UNIT.CC /1000) as Unit.GCT_Score,
num(avg(UNIT.M_M / 1000) + Avg(UNIT.AGE) + Avg(UNIT.FIX*10) + Avg(UNIT.CC /1000)) as Buyer.GCT_Score,
The first one (unit score) works brilliantly but when I load the script with the buyer score expression I get an error and it says I have an invalid expression in my script.
Does anyone have any ideas? Any help would be very much appreciated.
If you're using Avg() you're aggregating the data - are you using a Group By statement when loading? If not, you'll need to include the fields you want to group by for your aggregation in that statement.
Whenever you use aggregation function in your load statement, you must use group by statement.
num(avg(UNIT.M_M / 1000) + Avg(UNIT.AGE) + Avg(UNIT.FIX*10) + Avg(UNIT.CC /1000)) as Buyer.GCT_Score
group by ID;
Ok so like I said that worked brilliantly but now I want to add an if to the expression.
This is my script,
if(UNIT.GRADEFIX = '' , '', num(avg(UNIT.M_MILEAGE / 1000) + Avg(UNIT.AGEMONTHS) + Avg(UNIT.GRADEFIX*10) + Avg(UNIT.CAPCLEAN_ORIG /1000))) as Buyer.GCT_Score
group by %_BNO;
It's again saying invalid expression?