6 Replies Latest reply on Feb 15, 2020 2:40 AM by Bhadresh Maniya

    Aggregating phases and running total

    Soumya Samal

           I have a request from the customer

      I will have to aggregate the subject and the values and keeping the date 1's first value date 2 1st value and date 3 last value.

       

      please note that the value column is a running total by phase. like in xyz1 its existing value (300) + 90 = 390 and xyz2 is 390 + 127=517

       

       

      SubjectValuedate 1date 2date 3
      Existing Value0nullnullnull
      Abc1512/1/20202/4/20201/21/2023
      Abc1512/2/20203/4/20201/22/2023
      Abc1112/3/202012/3/20201/23/2023
      Abc1112/4/202012/8/20201/24/2023
      Abc1212/5/20201/2/20211/25/2023
      Abc1212/6/20201/2/20221/26/2023
      Abc2212/7/20204/4/20221/27/2023
      Abc2512/8/20202/23/20231/28/2023
      Abc2112/9/20202/24/20231/29/2023
      Abc2212/10/20202/25/20231/30/2023
      Abc2312/11/20202/26/20231/31/2023
      Abc2412/12/20202/27/20232/1/2023
      Abc2412/13/20202/28/20232/2/2023
      Abc2412/14/20203/1/20232/3/2023
      Existing Value300nullnullnull
      xyz1102/3/20212/28/20232/3/2023
      xyz1202/4/20213/1/20232/4/2023
      xyz1402/5/20213/2/20232/5/2023
      xyz1202/6/20213/3/20232/6/2023
      xyz2152/7/20213/4/20232/7/2023
      xyz2252/8/20213/5/20232/8/2023
      xyz2352/9/20213/6/20232/9/2023
      xyz2522/10/20213/7/20232/10/2023

       

       

       

      Result

         

      SubjectValuedate 1date 2date 3
      Existing Value0nullnullnull
      Abc11612/1/20202/420202/3/2020
      Abc22512/7/20204/420221/27/2023
      Existing Value300nullnullnull
      xyz139012/7/20204/4/20222/6/2023
      xyz25172/7/20213/4/20232/10/2023

       

       

      Let me know for any questions.

       

      Thank you

        • 1. Re: Aggregating phases and running total
          shruten parmar

          Hi Soumya,

           

          First thing in data, you need to specify/ rename Existing belongs to which subject.

          Something like : Existing Value_ABC/  Existing calue_XYZ

           

          Else tableau will not get to know how to aggregate existing value to what.

           

          If possible you can also keep new column for existing value of ABC/XYZ group

           

          Thanks,

          Shruten

          • 2. Re: Aggregating phases and running total
            Zhouyi Zhang

            Hi, Soumya

             

            How did you distingush the two "Existing value", which one should be phase I and which is phase II?

            When tableau loads the data, it doesn't know this unless there is a column to indicate it.

             

            ZZ

            • 3. Re: Aggregating phases and running total
              Bhadresh Maniya

              Hi Soumya,

               

              I have achieved Value as per your requirement. I am not understand how to show Date1, Date2, Date3.Can you please explain for dates?

               

              • 4. Re: Aggregating phases and running total
                Soumya Samal

                Hi ZZ

                 

                Each subject has a category ABC or XYZ , and each category has sub categories like ABC1 or XYZ1. Existing Value is a start where there is a value. Its not a column its a row within the column subject.

                 

                Please let me know if this clarifies your question.

                 

                Thanks

                • 5. Re: Aggregating phases and running total
                  Soumya Samal

                  Hi Bhadresh Bhadresh Maniya,

                  you can take the minimum of Date 1 , Min of date 2 and Max of Date 3 . But please note that there are null values above them.

                  Please let me know if this clarifies.

                  • 6. Re: Aggregating phases and running total
                    Bhadresh Maniya

                    Hi Soumya,

                     

                    Follow below steps to achieve your requirement

                     

                    Step 1: You need to change Date 1, Date 2 and Date 3 Data Type String to Date.

                     

                     

                    Step 2:  Create One calculated dimensions which name as Subject1

                     

                    IF [Value]=0 THEN

                    [Subject]+' '

                    ELSE

                    [Subject]

                    END

                     

                    Step 3: Right click on above calculated dimension and create Group as below screenshot.

                     

                     

                    Step 4: Create Group as per below screenshot

                     

                     

                    Step 5:  Create 1 Calculated measure and 3 Calculated Dimensions which us as below:

                     

                    Value Cal:

                    { FIXED [Subject1 (group)],[Subject1] : SUM([Value]) }

                     

                    Date1:

                     

                    { FIXED [Subject1]: MIN([Date 1]) }

                     

                    Date2:

                     

                    { FIXED [Subject1]: MIN([Date 2]) }

                     

                    Date3:

                     

                    { FIXED [Subject1]: MAX([Date 3]) }

                     

                    Step 6: Drag and Drop below screenshot dimensions and measure. Right click on 'Value Cal' measure and select 'Discrete'.

                     

                     

                     

                    Step 7: Drag and Drop Calculated Date1,Date2 and Date3 in Rows Section

                     

                    Step 8: Right Click on Date1 and select Minimum. Right Click on Date2 and select Minimum. Right Click on Date3 and select Maximum.

                     

                     

                    Step 9: Right Click on 'Value Cal' and select Running Total

                     

                     

                    Step 10: Again Right Click on 'Value Cal' and select 'Edit Table Calculation...'

                     

                     

                    Step 11: Follow below screenshot

                     

                     

                    Step 12: Right Click on 'Subject1(group)' and Deselect 'Show Header'.

                     

                     

                    Step 13: Expected output as below.

                     

                     

                    Let me know if you have any issue.

                     

                    Please find attached workbook for your reference.