topic Re: Critical Report in QlikView

Critical Report

Mon, 13 Feb 2012 21:13:17 GMT

I'm not sure where to begin this post.

I have three 'job files'.

'Job A', 'Job B', and 'Job C'. Each job is created with 3rd party software, and all of the job data is synced to a single SQL Server 2005 database.

Job B, and Job C, are a clone of Job A. Every Job has similar columns.

Each job has 'Job Properties', and within each set of 'Job Properties', there are 20 user tag codes.

Using 'Usertag8code', I've defined what type of job, 'Job A', 'Job B', and 'Job C' are.

Job A is equal to Baseline Budget.

Job B is equal to Working Budget.

Job C is equal to Current Estimate.

And last but not least, for dimension purposes, each job has the same job description.

_________________________

Within QlikView, my dimensions are as follows:

Jobs.Description

ProgressItems.WBSCode (Item Tracking Number)

ProgressItems.Description (Item Description)

Within QlikView, my expressions are as follows:

[Baseline Budget] = if (JobsView.UserTag8Code = 'Baseline Budget', ProgressItems.TotalCostPlanned)

[Working Budget] = if (JobsView.UserTag8Code = 'Working Budget', ProgressItems.TotalCostPlanned)

[Current Estimate] = if (JobsView.UserTag8Code = 'Current Estimate', ProgressItems.TotalCostPlanned)

_________________________

Each column is calculating the same table field from all three jobs, IF the job has a usertag8code of (one of the above).

For some reason, I'm only getting a return on the Baseline Budget column. The others remain a dash, as if it isn't recognizing the usertag.

I'm hoping someone can point me in the right direction, am I using the wrong syntax?

Critical Report

Mon, 13 Feb 2012 21:24:37 GMT

could you post a QVW with an example of your info?

Re: Critical Report

Mon, 13 Feb 2012 21:58:16 GMT

How do I attach a file?

Edit: Nevermind, I figured it out. Please find attached "Budget Forecast.qvw".

Edit: I'm not sure how much data you'll get returned from just the QVW, hopefully you can help me though?

Thanks again.

Re: Critical Report

Mon, 13 Feb 2012 23:14:25 GMT

it works if you use set analysis, for example:

Sum ({<JobsView.UserTag8Code = {'Current Estimate'}>} ProgressItems.TotalCostPlanned)

but there seems to be something odd in the data model, because the total is the same for every UserTag8Code

Re: Critical Report

Mon, 13 Feb 2012 23:18:13 GMT

Is this what you want?

I use set analysis instead of the if

Critical Report

Anonymous — Tue, 14 Feb 2012 00:19:14 GMT

There should be aggregated function in the expressions, I guess sum() in this case:
= sum(if (JobsView.UserTag8Code = <condition>, ProgressItems.TotalCostPlanned))