Distinguishing between 5 different values and calculating the cost for each value in their own column

[#1]

Some much needed help on this would be very much appreciated. I couldn't think of a title to best fit what I was trying to ask, but I hope someone will understand and provide a suitable solution, as my head is about to explode.

I'm trying to calculate the cost of work done for the different offices outside its own office (different regions within our company)

So a Project may be awarded in the north office but a salesperson from either the south, east or west office may complete a number of activities amongst many of the activities within that project.

I need a column for each different office showing the total cost for the current selected month (PeriodDate). Something like this:

So for this ProjectNo (a dimension in my straight table), sum the Cost of the activities which have a SalesPerson with a StaffOffice not equal to the projects Office

This is how my tables link up: (As daft as it seems there is no StaffNo FK in the activities table)

Project	Activity	Staff
ProjectNo - PK	ProjectNo - FK
Office	SalesPerson	SalesPerson
ProjectTotalValue	Cost	StaffOffice
	PeriodDate	StaffNo PK
	ActivityNo PK
	Status

The user has two key selections to filter the projects, one by the ‘PeriodDate’ which I was using this to show the completed value for current and previous month:

SUM({<Status={'COMPLETED'}, PeriodDate={"$(=date(addmonths(PeriodDate,-1),'YY-MM'))"}>} Cost)

The other option is by ‘Office’ dictating which projects are shown.

Thank you in advance for taking the time to read this.

Regards

Richard

[avinashelite]

Hi Richard,

Can u please post your app, so that will get better understanding of the data and problem.

Regards,

@vi

[DavidFoster1]

A table including Staff Office and a Cost expression should show the breakdown costs. Your set expression is not helping though. If you want current month and previous month then you have a couple of options:

1) Have a calendar that identifies these months and then a filter button that selects these months

2) Include both months in you set analysis either by have them listed or by using the + operator on two separate sets.

[struniger]

Wouldn't a very simple SUM(IF(Office <> StaffOffice, Cost)) do the job?

Best regards

Stefan

[#5]

Thank you all for getting back to me, I have attached a sample as Avinash R suggested, so hopefully you will better understand the problem.

David Foster: I have two list boxes, one for the PeriodDate displaying the months as 14-06 (June 2014) etc and then the other displaying the offices

My expression does work in my straight table for the purpose of showing current month:

SUM({<Status={'COMPLETED'}, PeriodDate={"$(=date(addmonths(PeriodDate,-0),'YY-MM'))"}>} Cost)

and previous month:

SUM({<Status={'COMPLETED'}, PeriodDate={"$(=date(addmonths(PeriodDate,-1),'YY-MM'))"}>} Cost)

Stefan Truniger:

That expression does work, but I believe it's adding up all the other offices in one column. Where as I would like to show it like this:

			Value by Office for Current Period
Office	Project	Project Total	North	South	East	West	Current Period Total
North	PR0001	£2000	£300	£0	£100	£100	£500

Regards

Richard

[struniger · Accepted solution]

How about this? See attached

[DavidFoster1]

Like this?

Uses AGGR statement to provide the project overall costs as a calculated dimension while the PIVOT table presents the costs by Staff office.

[#8]

Hi Stefan

Thank you very much for that, exactly what I needed. I did adapt it slightly so I could have it in a straight table, mainly because there was value completed which hadn't been allocated to a staff member and I wanted to identify this.

sum({<A_S={'Completed'}, ACTIVITY_PERIODDATE={"$(=date(addmonths(INVOICE_PERIODDATE, 0), 'YY-MM'))"}>} IF(STAFFOFFICE ='ANO-Asbestos North', COST))

My apologies for the delayed response, it took me a while to get a developer license so I could view the both your's and David's test sample.

David Foster: Thank you for your help but unfortunately it wasn't quite what I was looking for.