Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have some data as follows ( there are more like 20 different impacted business and Delivery there are many more options too)
Project Number Project Hours Delivery Impacted business
PRJ001 20 Q1 2016 Delivered EQUITY MKTS
PRJ002 50 Overdue RATES
PRJ003 10 On Hold EQUITY MKTS
PRJ004 30 Q2 2016 Delivered EQUITY MKTS
PRJ005 10 Q2 2017 Planned RATES
PRJ006 5 Q4 2016 Planned EQUITY MKTS
PRJ007 90 Q2 2015 Delivered EQUITY MKTS
I have built the following pivot table where the idea is to show the count of projects, the number of hours the account for, the cumulative hours as you go further right, and the quarterly target (this is picked up from another table which has Impacted business and target in)
I built this pivot table with 2 Dimensions: Impacted Business and " =if(WildMatch(Delivery,'Skipped', 'On Hold','Overdue','*$(=vCurrentYear)*'),Delivery)" as I only want to deliveries for this year or projects which are on hold/overdue/skipped.
It has expressions:
1. Number of projects =count(distinct ([Project Number]))
2. Hours =num(sum(YearlyHours),'##.')
3. Cumulative Hours - This is the part I need help with
4. Quarterly Target:
if
(
WildMatch(Delivery,'Skipped','Overdue','On Hold'), Null(),
if
(Delivery like 'Q1 *',[2016 Target]/4,
if
(Delivery like 'Q2 *',[2016 Target]/2,
if
(Delivery like 'Q3 *',[2016 Target]*0.75,
if
(Delivery like 'Q4 *',[2016 Target],
)
))
)
)
For cumulative hours the code I have at the moment is as follows. You can see from the diagram that hours and cumulative hours at the moment are the same. I would like to get it so that Q2 2016 delivery cumulative hours is the sum of Q1 2016 delivered + Q2 2016 delivered. I have played around with aggr and range sum (which is what my last 2 rows are for that are not calculating!) but i cant seem to get it to calculate as i need it. I understand why its doing what its doing... i guess its taking the columns as a type of filter... but how can i bypass this?
if
(
WildMatch(Delivery,'Skipped','Overdue','On Hold'), Null(),
if
(Delivery='Q1 ' & $(=vCurrentYear) & ' Delivered',sum({<Delivery ={$(=vQ1Delivered)}>}YearlyHours)
,
if
(Delivery='Q1 ' & $(=vCurrentYear) & ' Planned',sum({<Delivery ={$(=vQ1Delivered),$(=vQ1Planned)}>}YearlyHours)
,
if
(Delivery='Q2 ' & $(=vCurrentYear) & ' Delivered',sum({<Delivery ={$(=vQ1Delivered),$(=vQ1Planned),$(=vQ2Delivered)}>}YearlyHours)
,
if
(Delivery='Q2 ' & $(=vCurrentYear) & ' Planned',sum({<Delivery ={$(=vQ1Delivered),$(=vQ1Planned),$(=vQ2Delivered),$(=vQ2Planned)}>}YearlyHours)
,
if
(Delivery='Q3 ' & $(=vCurrentYear) & ' Delivered',sum({<Delivery ={$(=vQ1Delivered),$(=vQ1Planned),$(=vQ2Delivered),$(=vQ2Planned),$(=vQ3Delivered)}>}YearlyHours)
,
if
(Delivery='Q3 ' & $(=vCurrentYear) & ' Planned',sum({<Delivery ={$(=vQ1Delivered),$(=vQ1Planned),$(=vQ2Delivered),$(=vQ2Planned),$(=vQ3Delivered),$(=vQ3Planned)}>}YearlyHours)
,
if
(Delivery='Q4 ' & $(=vCurrentYear) & ' Delivered',sum({<Delivery ={$(=vQ1Delivered),$(=vQ1Planned),$(=vQ2Delivered),$(=vQ2Planned),$(=vQ3Delivered),$(=vQ3Planned),$(=vQ4Delivered)}>}YearlyHours)
,
if
(Delivery='Q4 ' & $(=vCurrentYear) & ' Planned',sum({<Delivery ={$(=vQ1Delivered),$(=vQ1Planned),$(=vQ2Delivered),$(=vQ2Planned),$(=vQ3Delivered),$(=vQ3Planned),$(=vQ4Delivered),$(=vQ4Planned)}>}YearlyHours)
) ) ) ) ) ) ) ))
Thanks,
Lizi
Accepted Solutions
Sorry, yes, I have attached a sample to original post (sorry I cant share my real application due to sensitive info)
Cumulative hours row should show null for on hold, overdue or skipped columns, but for Q1/Q2/Q3/Q4 it should accumulate 'hours' as you go right. like so:
