Can someone help on some complex scenario in Spotfire calculation

[gurusai sankar]

Hi All,

I have the data like Capacity Hours, Allocation Hours , Period, Resource pool, Resources, Project name, and manager name. I want to show capacity hours and allocation hours periodicallly in the bar chart per Resource pool and resources. For each resource assign multiple projects and having capacity and allocation, even one resource allocated multiple projects, capacity hours should be unique. it won't change for every project, I am doing some calauclation - but i am not getting the output as expected. please find below example and help on this issue.

In my Report output :

Resource Projects Manager Capacity

A x ABBB 100

Y ABBB 100

Z ABBB 100

Grand Total : 100

Expected Output:

A x ABBB

Y ABBB 100

Z ABBB

 

Grand Total : 100

 

Insted of showing every project individual capacity - it should show only one time as it is unique, it should be the same for every project. I have used distinct function and some calaucltions , but i am not getting the right output as expected., as per my calcultion - for some of the resources - for some projects i am getting the correct capacity and some other procets getting zero which is not coreect , if one project is having 100 capacity for that resource, for rest of the projcets also should have 100. (ex for my wrong output:

Resource ;Dinesh D (Nov 2018 )

Elizabeth Schulze (feb 2019)

 

I took some sample data and created .

 

Please find attached dxp file and kindly help on this issue.

[Gaia Paolini]

Thanks for the dxp. I understand the problem is with the cross table. However I don't have the context and there are lots of columns. The filter on period (top left) does not select one period at a time and capacity does change with period. Maybe it would help if you clarified in words what you would like to see and explained the terminology.

Gaia

[gurusai sankar]

Thanks a lot Gaia for your quick responce. 

 

Actaully The thing is- 

 

 As per my custom expression in the chart- in cross table Capacity hours are showing "0" for some specific resource and project which is not correct as per our requirement. Capacity hours should not be chnage and should not be a zero. For every resource -for every project capacity should be same. even one one reosurce  allocated multiple projects- capacity should be only one number. Ex: Let me take ,y scenario. 

 

My isssue: 

 

If you search resource called "Dinesh D "  and clicked on   Nov 2018  in the bar - you will get the details in the below cross table . if you can observe capacity hours- it is showing "0" for one of the project and rest all are having "168". i am unable to find how "0" is coming for that specific resource and project. Actaully capacity hours should not be zero and different number and should not change for each project.   it is the unique for each resource. it should not change and not be a zero for each project. 

 

In my Report output :

 

Resource            Projects            Manager            Capacity              

 

A                            x                       ABBB                 100 

 

                               Y                      ABBB                  100

 

                                Z                      ABBB                  100

 

                   Grand Total :                                            100

 

Expected Output: 

 

A                            x                       ABBB                 

 

                               Y                      ABBB                  100

 

                                Z                      ABBB   

 

 

 

Grand Total :                                                               100               

 

 

 

The outcome of the issue is:

 

1. as i explanied in above scenario- in some places i am getting "0" capcity which is not right. please check and correct my custom expresssion and distinct caluclation. 

 

2. Is there a possibility to single line capacity number instaed of showwing every indivudual project.ex;  if one resource having 3 projects and showing every individual row capaity as 168 , we should consider as only one (168)

 

 

 

Hope this helps, 

 

 

 

kindly share your feedback

 

 

[James Watts]

I don't fully understand what your calculations are trying to do, but I suggest you do the following for a clearer picture of what is happening:

- duplicate the cross table

- convert the new cross table to a table, showing only RESOURCE_NAME, PROJECT_NAME, CAPACITY_HOURS and Distinct

- add a column to the cross tab - "Count([RESOURCE_NAME])"

The cross tab will now show that the Count() value is four for Dinesh in Nov 18. You will see there are indeed four rows in the table, all with CAPACITY_HOURS of 168. You will also see that the Distinct column ranks all the rows for Dinesh, from 1-7. Going back to the calculation in the cross tab, your display of CAPACITY_HOURS is calculated as

case when count([RESOURCE_NAME])>1

then Sum(case when [Distinct]=1

then integer([CAPACITY_HOURS]) else 0 end)

else sum(integer([CAPACITY_HOURS])) end

as [CAPACITY HOURS]

So, in the cross tab, COUNT([RESOURCE_NAME]) is bigger than one, but [Distinct] is not 1 for the R*DS project, so the calculation returns 0.

If you just change the calc to Avg([CAPACITY_HOURS]) you will see the value you are after.

[gurusai sankar]

HI JIM, Thanks a lot for your prompt responce,  i have replaced as suggested in the below, but could not get the right data and totals are also changing. case  when Avg([CAPACITY_HOURS])>1

        then Sum(case  when [Distinct]=1

             then integer([CAPACITY_HOURS]) else 0 end)

              else sum(integer([CAPACITY_HOURS])) end

       as [CAPACITY HOURS]As per above scenario (Dinesh,Nov 2018)- i am expecting 168 capacity insted of showing zero as rest all having 168, and grand totals aslo should be 168. Kindly share your comments

[James Watts]

I still don't see what this calculation is trying to achieve. Simply putting

Integer(Max([CAPACITY_HOURS])) as [CAPACITY_HOURS]

as the first column of the cross tab will do what you are trying to achieve.

The other option is to move the [CAPACITY_HOURS] column to be the second column of the other axis; this will display it only once for all the projects.

I've attached a file demonstrating this.

[gurusai sankar]

It worked . Thanks a lot Jim for your great help here. 

 

I am also struggling with reset all filter except period range filter on the top of the text area for every page filtering schema. It would be very helpful to provide the solution. 

 

https://community.spotfire.com/questions/how-fix-reset-all-filter-except-one-filter-text-area

 

 

[gurusai sankar]

Hi Jim,  

 

Sorry to keep disturbing you. kindly provide your help. Still i am facing some problem with capacity report caclulation issue. attched dxp file demonstartion which you had done. 

 

1st Thing:

 

Generally max (capacity _hours)  gives correct output when select indivudual resource but when we select all - grand totals are not coming as expected. actaully logic should be satisfy both in the below example. 

 

ex for selecting indivudual resource : (it is comimg as per your logic) 

 

Resource       Project 

 

A                   ABC             168

 

                      CFT             168

 

                      GHY            168

 

Total capcity of Resource A ; 168   (eventhogh one resource allocated multiple projects , we should consider only one capacity hours for total , it is a uniq number for every resource (168)                 

 

For selecting all resource : 

 

A                   ABC             168

 

                      CFT             168

 

                      GHY            168

 

Total capcity of Resource A ; 168

 

B                  GHF             152

 

                      HJU            52

 

                      JKN           152

 

Total capcity of Resource B ; 152

 

Grand total of capacity should be (A + B ) = 168 + 152 =  320 

 

2nd Thing : 

 

I have also the data -CAPACITY_FTE , ALLOCATION_ FTE , AVAILABILTY FTE (CAPACITY_FTE - ALLOCATION FTE) 

 

My requirement is - there should be drop down for HOUR & FTE , when we select HOUR & FTE drop down filter it should change dynamically in the chart and when we marked in the chart- cross table also should change, my logic is working fine in the chart but doesnot for cross table as are getting "0" in sometimes. now we should fix all possibilities. i have tried and could not achieve. 

 

dropdown fixed values : 

 

HOUR:  case  when count([RESOURCE_NAME])>1 then Sum(case  when [Distinct]=1 then integer([CAPACITY_HOURS]) else 0 end) else sum(integer([CAPACITY_HOURS])) end as [CAPACITY HOURS], Sum(integer([ALLOCATION_HOURS])) as [ALLOCATION HOURS], (case  when count([RESOURCE_NAME])>1 then Sum(case  when [Distinct]=1 then integer([CAPACITY_HOURS]) else 0.0 end) else sum(integer([CAPACITY_HOURS])) end) - Sum(integer([ALLOCATION_HOURS])) as [AVAILABILITY HOURS]

 

FTE : case  when count([RESOURCE_NAME])>1 then Sum(case  when [Distinct]=1 then integer([CAPACITY_FTE]) else 0 end) else sum(integer([CAPACITY_FTE])) end as [CAPACITY FTE], Sum(integer([ALLOCATION_FTE])) as [ALLOCATION FTE], (case  when count([RESOURCE_NAME])>1 then Sum(case  when [Distinct]=1 then integer([CAPACITY_FTE]) else 0.0 end) else sum(integer([CAPACITY_FTE])) end) - Sum(integer([ALLOCATION_FTE])) as [AVAILABILITY FTE]

 

i am brainstroming for last couple of weeks on this issue. Kindly help on this. Sorry to keep disturbed.