Straight Table expression using a calculated field

[#1]

Hi All,

I have a straight table of employee related data (days worked, contracted days etc.) that contains a number of calculated fields, one of which is 'overtimedays'. I am trying to put this field into another expression in the same table:

Sum({< [Entry Date] = {">$(=Date(Today() - ((17 * 7)+ [overtimedays] )))"}, Project_Type = {"Project", "Non_Project"} >} [Total Hrs])

However, this doesn't seem to work. If i replace [overtimedays] with a number, the expression works fine.

Any ideas how I can make this type of calculation work? I'm fairly new to QlikView.

Thanks!

[sunil2288]

HI Rbi,

Can you explain What you exactly want to do..

[jonathandienst]

Hi

I have noticed this problem - I had a similar problem (using Column(#) rather than the column header - but its all the same, I think). It seems that you can refer to other columns in calculations (eg Column(1)/(Column(1) + Column(2)), but not in conditional expressions. This expression did not work:

Count(If(ValueDate = Date(Column(1)), BalanceRemaining))

But if I replaced Column(1) with a literal date (the value in Column(1)), it worked OK. See post http://community.qlik.com/forums/t/33430.aspx

It wont work in a set expression, because set expressions are applied on the model as a whole and are not at a row level. It may may work if you replace [overtimedays] with the expression. Not very elegant, I agree

Jonathan

[#4]

The expression I posted is designed to show the total number of hours worked on "project" and "non-project" activities over a time period of 17 weeks + overtimedays (17*7 because our working time is recorded per day).

[johnw]

A set analysis set is calculated once for your entire table. You can't (meaningfully) refer to dimension values in it, calculated or otherwise. You'll have to write that part with an if(). Somthing like this:

sum({<Project_Type={'Project','Non_Project'}>} if("Entry Date">today()-((17*7)+"overtimedays"),"Total Hrs"))

[#6]

I don't seem to be able to get that kind of IF expression to work either!

[jonathandienst]

As I stated earlier, I would not expect that to work because "overtimedays" is a column in your table and these seem not to work in conditionals. With all due respect to JohnW, who knows far me than me!

Take JownW's expression, but replace "overtimedays" with the expression for that column. That might work, but I can't say for sure as I have not seen your data model or QVW file.

Jonathan

[jonathandienst]

You could also test JohnW's expression without the "overtimedays" and if it works, you know that the expression logic is correct. Then try what I suggested above.

sum({<Project_Type={'Project','Non_Project'}>} if("Entry Date">today()-(17*7),"Total Hrs"))

Jonathan

[#9]

OK, so John W's formula works if i take out any reference to "Overtimedays", but not with reference to the field or with the expression added.

Thanks for your help so far guys, i'll keep trying!

[johnw]

Well, if you can't refer to column by name or number in a conditional, you can always repeat the column expression. If it's large, you could make it a variable and use the variable in both columns.