Critical Report

[#1]

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?

[#2]

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

[#3]

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.

[#4 · Accepted solution]

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

[#5]

Is this what you want?

I use set analysis instead of the if

[Anonymous]

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