Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
How about this? See attached
Hi Richard,
Can u please post your app, so that will get better understanding of the data and problem.
Regards,
@vi
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.
Wouldn't a very simple SUM(IF(Office <> StaffOffice, Cost)) do the job?
Best regards
Stefan
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)
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
How about this? See attached
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.
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.