1 Reply Latest reply on Oct 7, 2018 8:17 PM by anh chu

    Obtain previous value of a dimension based on date/time

    anh chu

      Hi guys,

       

      I have been trying to work on this problem for a couple of days now. It is simple if use table calculation Lookup() function. But I need it to be calculated field so I can use it in different views without the presence of the dimensions that dictate the table calculation. Please help me if you can.

       

      *I have to inner join 2 tables by Ticket ID: Ticket Management and Assignment History. These 2 tables don't have the same number of Ticket ID. Ticket Management only has 1 ticket ID per row, Assignment History has duplicate ticket ID associated with the Assignment History => Therefore, I have to use ATTR() in several of my codes. That's why I cannot really combine LOD and table calculation

       

      Below is the mock-up of my data.

      Ticket ID

      Assignment D/T

      (Calculated field)

      Department assigned the ticket

      Department in-charge

      (Calculated field)

      Re-assigned Yes/No

      01

      1/1/2018

      Initial assignment

      ABC

      No

      01

      1/2/2018

      ABC

      DEF

      Yes

      01

      1/3/2018

      DEF

      DEF

      No

      01

      1/4/2018

      DEF

      DEF

      No

      01

      1/5/2018

      DEF

      GHI

      Yes

      02

      1/1/2018

      Initial assignment

      GHI

      No

      02

      1/2/2018

      GHI

      GHI

      No

      02

      1/3/2018

      GHI

      DEF

      Yes

       

      I want to create 2 calculated fields:

                

                1. [Department assigned the ticket]: is the department assigned the ticket to the [Department in-charge]. Basically, for each row of each [Ticket ID], I need to look up the [Department in-charge] with the previous [Assignment D/T], and fill that value in this [Department assigned the ticket] field

       
                2. [Re-assigned Yes/No]: if [Department assigned the ticket] = Department in-charge then "Yes" else "No" end

       

      I was able to use Lookup() function calculated along Ticket ID with Assignment D/T sorted ascending, to lookup previous value of Department in-charge to fill in [Department assigned the Ticket] field. But this approach requires both Ticket ID and Assignment ID in the view. Besides, because the [Department assigned the ticket] is table calculation, the [Re-assigned Yes/No] is also table calculation.

       

      I want to be able to build below view based on [Ticket ID] and [Re-assigned Yes/No] fields only:

      Ticket ID

      Re-assigned among groups

      (count number of "Yes" in the [Re-assigned Yes/No] field)

      01

      2

      02

      1

       

      At first, I feel like this is a pretty simple problem. But still now I cannot find any function to help me except for table calculation, which I'm trying to avoid.

       

      Thank you so much for any help

       

       

      From <https://community.tableau.com/discussion/create!input.jspa?containerType=14&containerID=2003>

       

        • 1. Re: Obtain previous value of a dimension based on date/time
          Michel Caissie

          Anh,

           

          For this type of problem you will need to use table calculations. But the fact that you need to bring all dimensions in the view for the calculation doesn't mean that you have to show all those dimensions or all the rows resulting of those calculations.

           

          If you look in the attached,  on the sheet ValidateData,  I computed for each row if it as been reassigned with

          if index() > 1 then

          if LOOKUP(ATTR( [Department] ),-1) = ATTR( [Department] )  then 'No' else 'Yes' end

          else 'No' end

           

          Next for each ID I compute the WindowSum or reassignments with

          WINDOW_SUM( if [isReassigned] = 'Yes' then 1 else 0 end )

          note: this is a nested calculation, so you have to apply the computation on both calculations.

          This gives me the number you are looking for  on every ID rows.

           

          Next I add another calculation returning true on the first  ID row.

           

          Next I duplicate this sheet, which becomes the final view sheet.

          I right-click the blue pills for Assignment and Department  and uncheck  'Show Header'  to hide those dimensions.

          I remove the isReassigned calculation which I don't need.

          Finally, I move the Keep a single row calculation  on the filter shelf and keep the true values, to hide all other rows.

           

          Michel