gurusai sankar Posted May 7, 2019 Share Posted May 7, 2019 Hi All, PFA- dxp file i have the data like capacity fte , Allocation fte, Availabilty fte. Avilability = [Capacity fte] - Allocation fte] please follow the below scenario and request your help. select a resource called " Ziyu jin "in the resource filter and marked july 2018. and also set grnad total ,then we will get the output like below. Resource name project name capacity Fte Allocation Fte Availability fte Ziyu Jin Next gen,,,,,,,,,,,,, 1 0.12 0.88 Patient BI Analytics 1 0.14 0.86 Total 1 0.26 0.74 but expected senario is like in the below. we have to chnage avialbilty. availabilty should not be display for indivudual project for a resource. it should be calulate across all the projects fpr a resource.so that we are expecting avilabilty in the below. either we have to show grnd total value for all the rows else we should fix empty values for all the rows except grand total. Resource name project name capacity Fte Allocation Fte Availability fte Ziyu Jin Next gen,,,,,,,,,,,,, 1 0.12 0.74 Patient BI Analytics 1 0.14 0.74 Total 1 0.26 0.74 (or ) Resource name project name capacity Fte Allocation Fte Availability fte Ziyu Jin Next gen,,,,,,,,,,,,, 1 0.12 Patient BI Analytics 1 0.14 Total 1 0.26 0.74 Link to comment Share on other sites More sharing options...
James Watts Posted May 8, 2019 Share Posted May 8, 2019 Replace the third column with (case when count([RESOURCE_NAME])>1 then Sum(case when [Distinct]=1 then ([CAPACITY_FTE]) else 0 end) else sum([CAPACITY_FTE]) end) -(Sum([ALLOCATION_FTE]) over (Parent([Axis.Rows])))as [AVAILABILITY FTE] Link to comment Share on other sites More sharing options...
gurusai sankar Posted May 13, 2019 Author Share Posted May 13, 2019 Thanks you very much Jim, It was done. Can we show the grand total of only positive values (to avoild negative values) https://community.spotfire.com/questions/how-avoid-negative-values Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now