8 Replies Latest reply on Jul 27, 2016 2:53 PM by Chris Dickson

Getting first record from multiple records

satish.pandey Jul 27, 2016 2:39 PM

Hi,

I have a requirement where i am showing project related information and different tasks . My requirement is to get single records out of multiple records as attached.

Project Code TaskName Milestone Green Date

============== =========== =========== ===========

PJ00195 A EAC 5/31/2016

B EAC 6/9/2016

C REL 4/20/2016

D BET 8/4/2016

PJ01239 A ALP 5/31/2016

B EAC 3/1/2016

C ALP 6/30/2016

D DEV 8/4/2016

I want to pull very first date based on the green date. Like for project PJ00195, I want to show only TASK C, MILESTONE REL and Green Date 4/20/2016

Similarly for project PJ01239 i want to pull B, EAC, 3/1/2016. Please advise.

ATG Projects Weekly Status Report.twbx 181.6 KB

1. Re: Getting first record from multiple records

satish.pandey Jul 26, 2016 3:18 PM (in response to satish.pandey)
Like Show 0 Likes(0)

Actions
2. Re: Getting first record from multiple records

Chris Dickson Jul 26, 2016 3:22 PM (in response to satish.pandey)

Hi Satish,

create a calculated field MinDate= {fixed [Project Code]: min([Green Date)}

then create another [Green Date]=[MinDate], place this on the filter shelf and keep True
Like Show 0 Likes(0)

Actions
3. Re: Getting first record from multiple records

satish.pandey Jul 26, 2016 4:11 PM (in response to Chris Dickson)

Hi Chris,
Thanks for reply. Somehow its not working.

I have created two calculated fields
cal_MinDate = { FIXED [Project Code]: MIN([Milestone Green Date])} and
cal_GreenDate = cal_MinDate

When i drag cal_GreenDate, i dont see True/False value instead i see calendars having Years/Qtrs etc. as attached.
So i dont get to choose the option true false. Please advise
Like Show 0 Likes(0)

Actions
4. Re: Getting first record from multiple records

Chris Dickson Jul 27, 2016 12:21 AM (in response to satish.pandey)

Hi Satish,

apologies my instructions were not clear.

the second calculation should be cal_DateFilter = GreenDate=Cal_MinDate

As an aisde - i applaud your use of a prefix for calculation (i assume you have a fair bit of data warehousing in your past, but with tableau you don't necessarily need a prefix as the icon to the left of the field makes it clear if it is calculated or not with the = sign. If you are doing it for an organisation point of view you can collect fields and calculations into folders within your dataset if you want.
Like Show 0 Likes(0)

Actions
5. Re: Getting first record from multiple records

Ashish Chaudhari Jul 27, 2016 12:33 AM (in response to satish.pandey)
Hi Satish,

Create below calc field and put this in the filter and select True. Please find the attached workbook for the same (9.3).

[Green Date] = ({ FIXED [Project] : MIN([Green Date]) })

Refer the screenshot for the same and confirm the output.

Thanks and Regards,
Ashish Chaudhari

MIn Dates.twbx 15.9 KB
Like Show 0 Likes(0)

Actions
6. Re: Getting first record from multiple records

satish.pandey Jul 27, 2016 2:29 PM (in response to satish.pandey)

Thanks Ashish Chaudhari and Chris Dickson.
I absolutely got the logic. Somehow its not working in my case. Let me give you the exact scenario.

There is a field Milestone Green Date and when i pulled in the report it appeared as +Year and then i expanded and make it month and then i custom formatted to Day/Month/Year.
Now when i create formula and compare...its actually comparing with the original Milestone GreenDate with Year Value. I tried converting into the same format however i can only see false data. no true record is appearing.

Here is the formula: [Milestone Green Date]=({ FIXED [Project Code]: MIN([Milestone Green Date])})
This is in form of YYYY This is converted to MONTH/DD/YYY

I have also attached twbx for reference. Please see if you can advise.
Like Show 0 Likes(0)

Actions
7. Re: Getting first record from multiple records

satish.pandey Jul 27, 2016 2:38 PM (in response to satish.pandey)
Like Show 0 Likes(0)

Actions
8. Re: Getting first record from multiple records

Chris Dickson Jul 27, 2016 2:53 PM (in response to satish.pandey)

hi satish, you will need to extract the data for us to use your attached twbx, it is trying to get us to connect to your datasource.

right click on the datasource and extract then resave the twbx and attach again.
Like Show 0 Likes(0)

Actions