-
1. Re: Two Date fields and one measure
Patrick Van Der Hyde Dec 27, 2016 11:41 AM (in response to Sri K)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
-
2. Re: Two Date fields and one measure
Sri K Dec 28, 2016 2:20 AM (in response to Patrick Van Der Hyde)Hi Patrick,
Thank you for the response. I have gone through the links, there i didnt find the solution for my problem.
If you have time please look on my requirement.
Thank you in advance.
Thanks,
Sri
-
3. Re: Two Date fields and one measure
Shinichiro MurakamiDec 29, 2016 2:24 PM (in response to Sri K)
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
-
Filter_Start_Finish_SM_9.3.twbx 150.9 KB
-
-
4. Re: Two Date fields and one measure
Sri K Dec 29, 2016 9:14 PM (in response to Shinichiro Murakami)Hi Shini,
Thank you so much for your valueable time.
My database is Oracle and I am not getting Pivoting option there. For sample data I have taken excel datasource. Sorry I forgot to inform this before.
Please suggest me, how can I achieve this with oracle DB.
Thanks,
Sri
-
5. Re: Two Date fields and one measure
Sri K Dec 29, 2016 9:41 PM (in response to Shinichiro Murakami)Shini,
And I am using custom SQL here.
So please suggest me.
Thanks,
Sri
-
6. Re: Two Date fields and one measure
Shinichiro MurakamiJan 17, 2017 10:04 AM (in response to Sri K)
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
-
7. Re: Two Date fields and one measure
Yuriy FalJan 17, 2017 10:16 AM (in response to Shinichiro Murakami)
1 of 1 people found this helpfulSELECT
'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
-
8. Re: Two Date fields and one measure
Shinichiro MurakamiJan 17, 2017 12:19 PM (in response to Yuriy Fal)
Thank you Yuri
Shin