Chart with % of Total or All or some tricky combination?

[disqr_rm]

Hi Experts,

I have following 4 tables in QV document:

LinkTab:
LinkKey,
EmpNo,
Date;

Employee:
EmpNo,
CostCenter,
EmpName;

TimeLine:
Date,
Month,
Year;

Revenue:
LinkKey,
RevAmount;

What I would like to do is to place a chart on the Cockpit sheet which has Employee name and TimeLine as selection fields.

The chart would have 3 columns:
- Sum of Rev by selected employee in selected period
- % Rev with total Rev by all employees in his CostCenter in selected period
- % Rev with total Rev by all the employees in selected period

I know how this can be achieved with SET ANALYSIS but I am on QV 8.2 and cannot use SET ANALYSIS.

Is there anyway I can put this chart without generating separate tables totalling amount by cost center?

Appreciate your comments.

Thanks

[#2]

This should be doable with AGGR() and maybe a few TOTALs.

[disqr_rm]

I would hope so. Any idea how the expression would look like? Thanks for your help.

Rakesh

[#4]

Sure, just upload an example. Writing aggr statements without sample data is like playing darts blindfolded.

[disqr_rm]

That sounds fun though.

Please see enclosed example. The problem I have is when an employee or month is selected, sum(total ...) only gives data for the records in selection. but I need to get sum of all revenue in his cost center by all employee of that cost center.

Thanks for your help Jay.

Rakesh

[#6]

Ok, take a look at this. There's still some ambiguity about what you're looking for. Should the numbers not change when you select an Employee or Cost Center? That's easier to control through Set Analysis.

[disqr_rm]

Hi Jay,

Thanks for your response. Yes pretty close.

Actually, this is how far I could get as well. The problem is when you select an employee all number goes to 100%. Basically requirement is that chart should ignore the selections.

Usage is that I have a sheet calles "Employee Cockpit" which will tell us all about that employee. When you get to this sheet, you already have an employee selected. Then this cockpit has timeline at the top, when you can see the cockpit for a month or several for that employee. On this sheet I need to put a table showing revenue he brought in, % to the avg of other employees in his cost center, and finally % of the avg of the all employees of the company.

I know what I need is SET ANALYSIS, but I do not have that option in QV 8.2.

I guess I will have to build a separate table aggregating amount by cost center and month and then use that in the charts using "if" statements. Not something I realy like but do not see any other option.

Let me know what you think.

Regards

[#8]

Sorry! I forgot about that requirement half way through building this and I had to run. I'll have an answer for you late tonight.

[#9]

Well I thought there would be a way, but now I'm not so sure. After all, this is a good example of what Set Analysis allows us to achieve without data model changes!

Maybe repost it to see if someone else can come up with one. Sorry! I'll keep plugging away just for fun though.

J

[#10]

If there's a way, I sure don't know it. Aggr works within the selection and of course sum() does too. The only modifier that can look outside current selections is ALL, but that eliminates any grouping.

I'm so accustomed to being able to do this kind of thing that I forgot what was impossible just one version ago!