Impromptu and calculated columns

[slaforce]

Here is my situation. I need to create four calculated columns out of two attributes. My two attributes are weight and value. I have another attribute that is called transaction type. The values for transaction type are Actual and Estimate. From this, I need to create four columns
on my report: Actual Weight, Estimated Weight, Actual Value, Estimated
Value.

What is the best way to do this?

Thanks for the help.

 

[puppyquestion1]

Can you further describe the problem?

Are you:

1) trying to COUNT items that have Actual/Estimated weights and values, or

2) when a row is read, you want to put the actual "value" of weight or value in the appropriate column?

 

[slaforce]

I am trying to put the actual "value of weight and value into the appropriate columns. The report should look like this:

Actual Value Actual Weight Est Value Est Weight

 

[CanadianTechie]

slaforce,

Perhaps some sample data would help...but it sounds like you'll probably want to use some kind of If..then..else construct in your calculated column formula in order to key off the transaction type column and decide which is the appropriate value to return for the column in question...

Pedro Cardoso
A.K.A CanadianTechie

 

[slaforce]

My problem is I need to return both the Actual and the Estimate in one row. I think I need to do an alias table for Estimates. Then join the two tables together. My problem is when I try to do this - I am getting duplicate values. Here is what my data looks like:
I have a column called Job Number. For every job number there is a transaction code of actual and estimate. When the transaction code is actual - then the Value column is the Actual Value. When the transaction code is Estimate than the Value column is the Estimate Value. I need to compare Actual vs. Estimate value for every job number.

Thanks for your help

 

[CanadianTechie]

slaforce,

It sounds like your table has two rows that you want to display in your report as one row. What's the source database in this case? I'm "new" to Cognos technology, but what comes to mind is:

1) In the source database can you create a couple of view's, one returning t-code actual records and the other t-code estimate records -- then you could do the join in Cognos catalog with no problem. (You could actually create one view which would have one record per job number and two columns, one for each value, if you are more SQL savvy)

2) Edit the sql for the join's in the catalog directly to achieve a similar result as I described in 1...

Let me know how you make out...



Pedro Cardoso
A.K.A CanadianTechie

 

[griffindm]

slaforce,

Some test data, in column form, might make it easier for everyone to understand. I think Pedro has it right, and if so you just need to do a grouping on the common attribute of the two columns you need to summarize to get your result.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[slaforce]

Unfortunately - creating a view in the database is not an option. Here is some sample data from the database (it is a progress database)

Job: 12345
Transaction Type: A
Value: $100
Weight: 50
Date: 04-01-2003
Job: 12345
Transaction Type: A
Value: $200
Weight: 50
Date: 04-03-2003
Job:12345
Transaction Type: E
Value: $50
Weight: 25
Date 03-15-2003
Job:12345
Transaction Type: E
Value: $100
Weight: 50
Date 03-20-2003

I need the report to look like this:
Job Act Value Act Weight Est Val Est Weight
12345 $300 100 $150 75

However- it is looking like this right now:
Job Act Value Act Weight Est Val Est Weight
12345 $600 200 $300 150


I believe it is double counting the values because I have a self-join (alias table) to separate actual and estimate transaction types.

Any ideas?

Thanks for the help-

 

[GJParker]

You need to remove the alias table form your report and create four calculations one for each return value i.e.

Actual Value formula =
If Transaction Type = A then Value else 0

Actual Weight Formula =
If Transaction Type = A then Weight else 0

Estimated Value formula =
If Transaction Type = E then Value else 0

Estimated Weight Formula =
If Transaction Type = E then Weight else 0

If you group your report by Job number you can then place these formula on your report and sum at group level to give you the results you require.

HTH

Gary Parker
Systems Support Analyst
Manchester, England

 

[slaforce]

Thanks Gary -

This almost works. My problem is I need to filter based on the issue date of the actual transaction. So - If I filter for the month of April - I need to pull back all the actual values and weights for the jobs in April and the estimates associated with those job. However - the estimate could have happened in March. The field on the table is the same field (date). So - how would I filter on date for April and bring back all the estimates that were in March?

Thanks again for the help!

 

[slaforce]

Thanks Gary -

This almost works. My problem is I need to filter based on the issue date of the actual transaction. So - If I filter for the month of April - I need to pull back all the actual values and weights for the jobs in April and the estimates associated with those job. However - the estimate could have happened in March. The field on the table is the same field (date). So - how would I filter on date for April and bring back all the estimates that were in March?

Thanks again for the help!

 

[DoubleD]

This will push all the processing to the desktop, but you could do the following.
Group on Job Number.
Act Value = If Transaction Type = A then Value else 0
Act Weight = If Transaction Type = A then Weight else 0
Est Value = If Transaction Type = E then Value else 0
Est Weight = If Transaction Type = E then Weight else 0

Actual Date = Max(If Transaction Type = A then Date else Null)
Check the box that says calculate before summary filter is applied.

Now set your summary filter as follows:
Actual Date between 2003-04-01 and 2003-04-30

 

[slaforce]

Thanks for the suggestion. I think I'm getting closer - but not there exactly.

This solution takes all the actual transactions puts the highest date (of April) on them. I'm still picking up actual transactions from Jan - March. Is there a way- that I only pick up actual transactions from April - and not filter on estimate transactions.

 

[DoubleD]

If your Actual Date = Max(If Transaction Type = A then Date else Null) is associated to Job Number, the date it returns should always be the Actual date for that Job Number. All the Max function does in this instance is get you to a single record.

 

[slaforce]

Is max a function or is it a summary. I don't see it under function. I was using the maximum under summaries. It was taking the maximum date of all the values for that job number. I just want the values for the instances in April.
My results look like this:

Job Number Date Actual Date Actual Value Actual Weight
12345 1/1/03 4/1/03 $50 25
2/1/03 $10 10
4/1/03 $20 10


When I sum this up - I get an actual value of $80 instead of $20.

 

[DoubleD]

Assuming you will always want the estimates from the previous month, do the following.
Get rid of the Actual Date calculation above.
Get rid of your summary filter.
In your detail filter, put the following:
(Transaction Type = A and Date between 2003-04-01 and 2003-04-30) or (Transaction Type = E and Date between 2003-03-01 and 2003-03-31)
If you cannot safely assume your estimates are always in the previous month, then your table is not set up for the data you want to report. At this point, there would be no correlation between the Actual records for April and the Estimated records for March.

 

[slaforce]

Thanks for the advice. Unfortunately - the estimates are not always in the previous month. They can be anytime before the actuals.

 

[DoubleD]

Reading this I had one other thought. I would assume that the most recent estimate should match with the most recent actual. If this is true, you could try the following:

Actual Date = max(If Transaction Type = A then Date else Null)
Estimate Date = max(If Transaction Type = E then Date else Null)

Act Value = If Transaction Type = A and Date = Actual Date then Value else 0
Act Weight = If Transaction Type = A and Date = Actual Date then Weight else 0
Est Value = If Transaction Type = E and Date = Estimate Date then Value else 0
Est Weight = If Transaction Type = E and Date = Estimate Date then Weight else 0

Now set your summary filter as follows:
Actual Date between 2003-04-01 and 2003-04-30

 

[slaforce]

Thanks for the suggestion. I don't think this is going to work either. The problem I'm having with taking the max(date) is that it only gives me the latest transactions for that job. I need all the transactions for that month for the job.

Thanks for the help. I think I am going to try and get them to create a view. Can't think of a way to do this in Impromptu.

 

[milkiel]

Here is another way this might work.

Create the following calculated columns and group by Job:

Actual Date = If TransactionType=A and Date Between 2003-04-01 and 2003-04-30 then Date else Null
Est Date=If Transaction type = E then Date else Null

Act Value=total(If Transaction Type = A then Value else 0)
Act Weight=total(If Transaction Type = A then Weight else 0)
Est Value=total(If Transaction Type = E then Value else 0)
Est Weight=total(If Transaction Type = E then Weight else 0)

Detail Filter: (Actual Date is not missing or Est Date is not missing)
Summary Filter: (Act Value <> 0 or Act Weight <> 0)