topic Straight Table expression using a calculated field in QlikView

Straight Table expression using a calculated field

Thu, 02 Sep 2010 10:03:13 GMT

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!

Straight Table expression using a calculated field

sunil2288 — Thu, 02 Sep 2010 10:13:14 GMT

HI Rbi,

Can you explain What you exactly want to do..

Straight Table expression using a calculated field

jonathandienst — Thu, 02 Sep 2010 10:14:22 GMT

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

Straight Table expression using a calculated field

Thu, 02 Sep 2010 10:27:19 GMT

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

Straight Table expression using a calculated field

johnw — Fri, 03 Sep 2010 01:01:21 GMT

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

Straight Table expression using a calculated field

Fri, 03 Sep 2010 14:04:22 GMT

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

Straight Table expression using a calculated field

jonathandienst — Fri, 03 Sep 2010 14:51:12 GMT

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

Straight Table expression using a calculated field

jonathandienst — Fri, 03 Sep 2010 14:54:17 GMT

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

Straight Table expression using a calculated field

Fri, 03 Sep 2010 15:53:40 GMT

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!

Straight Table expression using a calculated field

johnw — Fri, 03 Sep 2010 16:43:21 GMT

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.

Straight Table expression using a calculated field

Mon, 06 Sep 2010 08:55:35 GMT

Could you explain how I make it a variable please?

Straight Table expression using a calculated field

johnw — Tue, 07 Sep 2010 17:38:33 GMT

As a simple example, let's say that your column expression is this:

sum(Sales)

You could create a variable (settings -> document properties -> variables -> new, among other methods) and set the value to sum(Sales). Then wherever you want to use it, use $(MyVariable) instead. The dollar sign expansion will then literally insert sum(Sales) where that expression is at execution time, so it's just as if you'd typed it in yourself, but it prevents dual maintenance when you're using the same expression in multiple places.

Straight Table expression using a calculated field

Wed, 08 Sep 2010 09:47:28 GMT

Thanks John! First attempt this appears to do the trick, but let me play with it a bit more to be sure. I really appreciate your help.