Sri,

Hello,  I moved this post to the Forums area.  It was posted in community canvas area accidentally.  

I am out sick today - the holidays and family visits seem to always result in getting a bit of a bug .  I saw the post and thought you might want to take a review of FAQ:  Open & Close Dates as there should be some examples there for you.  If not, let me know and I'll review this tomorrow or maybe someone else will chime in. 

Patrick 

Hi Sri

At first, edit datasource and pivot data.

Other than that, one filter affect to the project line item itself.

Then re-create calculated filed.

Overview: Level of Detail Expressions

[Start Date]

{fixed[Project Code]: min(if [Pivot field names]="Project Start Date" then [Pivot field values] end)}

[Finish Date]

{fixed [Project Code]:min(if [Pivot field names]="Project Finish Date" then [Pivot field values] end)}

Now data structure has been changed and you can handle each project with start/end date filter separately.

In the attached, "StartFinish" Tab shows total start and finish project number in year 2015~2017.

"StartFinish2" Tab only shows total start and finish project which started only in 2015~2017.

and you can add "on going project by adding one more calculated field

[Count of running_Proj]

running_sum(count(if [Pivot field names]="Project Start Date" then [Project Code] end))

-

running_sum(count(if [Pivot field names]="Project Finish Date" then [Project Code] end))

Thanks,

Shin

Thanks,

Shin

Sri,

I'm sorry I looked over this last request, but I'm not good at custom SQL.

Could some one else help to make data pivot happen on Oracle DB?

Thanks,

Shin

SELECT

  'project_start_date' as [pivot_field_name],

  [project_start_date] as [pivot_field_value],

  [...] --// all other field names from your table

FROM [your_table]

 

UNION ALL

 

SELECT

  'project_end_date' as [pivot_field_name],

  [project_end_date] as [pivot_field_value],

[...] --// all other field names from your table

FROM [your_table]

 

Hope this could help.

Yours,

Yuri

Thank you Yuri

Shin