11 Replies Latest reply on Dec 24, 2017 7:53 AM by Norbert Maijoor

    Selecting latest month value in a row

    Ruchi Deshpande

      Hi,

       

      I have the following view.This is a data of children nutrition grades. I have a variable "Time" which I have taken as Month. I want to calculate a variable Grade. This will select for each "childid" latest month grade. Here it should be grade of March. In case where March grade is NULL, it should select the previous month grade i.e. February. Where February grade is also NULL, it should select January grade.

       

       

      ChildId

      January

      February

      March

      Grade

      01

      Normal

      Normal

      SAM

      SAM

      02

      Normal

      Normal

      NULL

      Normal

      03

      MAM

      NULL

      NULL

      MAM

       

      How should I go about it. Please help. Since it is a view, I tried it with LOOKUP and PREVIOUS_VALUE. But ca't get it.

       

      -Ruchi

        • 1. Re: Selecting latest month value in a row
          Norbert Maijoor

          Hi Ruchi,

           

          Find my approach as reference below and stored on attached workbook version 10.3 located in the original thread

           

           

          1. Pivot the datasource first. Pivoting is explained here

           

          2. D1. Month converter:

          if [Pivot Field Names]="January" then 1

          elseif [Pivot Field Names]="February" then 2

          elseif  [Pivot Field Names]="March" then 3

          elseif  [Pivot Field Names]="April" then 4

          elseif [Pivot Field Names]="May" then 5

          elseif  [Pivot Field Names]="June" then 6

          elseif [Pivot Field Names]="July" then 7

          elseif  [Pivot Field Names]="Augustus" then 8

          elseif  [Pivot Field Names]="September" then 9

          elseif  [Pivot Field Names]="October" then 10

          elseif  [Pivot Field Names]="November" then 11

          elseif [Pivot Field Names]="December" then 12

          end

           

          3. D2. Last month with value: if(IsNULL([Pivot Field Values])=False) then ([D1. Month converter]) end

           

          4. D3. Max Month with Value: {fixed [ChildID]:max([D2. Last month with value])}

           

          5. D4. Display:[D1. Month converter]=[D2. Last month with value]

                                and [D2. Last month with value]=[D3. Max Month with Value]

           

          6. Drag required objects to the indicated locations and filter D4. Display onTrue

           

           

          Regards,

          Norbert

          1 of 1 people found this helpful
          • 2. Re: Selecting latest month value in a row
            Ruchi Deshpande

            Hello Norbert,

             

             

            Thank you so much for your reply. I found it helpful but still cannot get

            it right. Since, all my calculations are at LOD leve so I am finding it

            difficult to carry out further calculations on the calculated fields.

             

             

            I am attaching a packaged workbook, with my sample data.

             

            I want to do the following:

             

            I have 'childId' as rows and 'startedtime' taken as month/year as columns.

            For each calendar quarter I want to calculate the grades. For example

            'March Grades' will have the latest grade of the child for Jan,Feb and

            March. In case March grade is NULL, Feb grade should be latest and when

            both March and Feb grade is NULL Jan grade should be latest. This should

            happen for April - June; July-Sept; and Oct - Dec.

             

             

            After I have 'March Grades', 'June Grades' , 'Sept Grades' and 'Dec

            Grades'; I need to calculate Grade logic. I have shared the logic

            'Grade_logic' in the workbook.This logic looks at transition of the child

            from 'March Grades' to 'June Grades'; from 'June Grades' to 'Sept Grades'

            and so on.

             

             

            I quite managed it till this latest month grade logic was not there. But

            taking the latest month grade for a quarter is challenging for me. Also, I

            couldn't do it through Pivot tables as my data is not in that format.@

             

            Please help.

             

             

            Regards,

             

            Ruchi

             

            On Sat, Dec 16, 2017 at 5:45 PM, Norbert Maijoor <

            • 3. Re: Selecting latest month value in a row
              Norbert Maijoor

              Hi Ruchi,

               

              Find my updated approach below as reference and stored in attached workbook version 10.3 located in the original thread.

               

               

              1. Upfront is good to consider the following. If you apply the logic when NULL use the previous value than it won't be clear where the value comes from showing the NULL indicates there was "no progress" in the quarter and you could taken action on that. "Others"

               

              1. Define custom date Quarter

               

               

              2. D1. Grade first Quarter:

              if {fixed [Child Id],[D0. Started Time (Quarters)]:max([Started Time])}=[Started Time]

              and [D0. Started Time (Quarters)]=#1-1-2017# then [Interpret Grade] END

               

              3. D2. Grade second Quarter

              if {fixed [Child Id],[D0. Started Time (Quarters)]:max([Started Time])}=[Started Time]

              and [D0. Started Time (Quarters)]=#1-4-2017# then [Interpret Grade] END

               

              4.D3. Display Grade First Quarter

              {fixed [Child Id]:max([D1. Grade first Quarter])}

               

              5. D4. Display Grade Second Quarter

              {fixed [Child Id]:max([D2. Grade second Quarter])}

               

              6. D5. Grade_logic

              if [D3. Display Grade First Quarter] = 'normal' and [D4. Display Grade Second Quarter] = 'normal'  then 'Normal'

              elseif [D3. Display Grade First Quarter] = 'normal' and [D4. Display Grade Second Quarter] = 'MAM' then 'Newly Identified Malnourished'

              elseif [D3. Display Grade First Quarter] = 'normal' and [D4. Display Grade Second Quarter] = 'SAM' then 'Newly Identified Malnourished'

              elseif [D3. Display Grade First Quarter] = 'MAM' and [D4. Display Grade Second Quarter] = 'normal' then 'Graduate'

              elseif [D3. Display Grade First Quarter] = 'MAM' and [D4. Display Grade Second Quarter] = 'SAM' then 'Stagnant'

              elseif [D3. Display Grade First Quarter] = 'SAM' and [D4. Display Grade Second Quarter] = 'MAM' then 'Stagnant'

              elseif [D3. Display Grade First Quarter] = 'MAM' and [D4. Display Grade Second Quarter] = 'MAM' then 'Stagnant'

              elseif [D3. Display Grade First Quarter] = 'SAM' and [D4. Display Grade Second Quarter] = 'SAM' then 'Stagnant'

              elseif [D3. Display Grade First Quarter] = 'SAM' and [D4. Display Grade Second Quarter] = 'normal' then 'Graduate'

              else 'Others'

              END

               

              7 Drag required objects to the indicated locations and filter Weight Taken and Is Acive

               

               

              Let me know your thoughts....

               

              Regards,

              Norbert

              • 4. Re: Selecting latest month value in a row
                Ruchi Deshpande

                Hi Norbert,

                 

                Many thanks for this solution. As you have written, there are some NULL

                values displayed in "D3. Display Grade First Quarter". This happens when

                March and Feb both have NULL values with only Jan having valid responses. I

                want that "D3. Display Grade First Quarter" should capture Jan grades also

                when both March and Feb have NULL values.

                 

                Take a scenario when March and Feb both have NULL values and Jan has 'MAM'

                against Child id 01. For the same Child id D4. Display Grade Second Quarter has

                'normal' grade, so this progress will not be captured as D3. Display Grade

                First Quarter will have NULL.

                 

                Please help.

                 

                Regards,

                Ruchi

                 

                On Tue, Dec 19, 2017 at 3:26 PM, Norbert Maijoor <

                • 5. Re: Selecting latest month value in a row
                  Norbert Maijoor

                  Hi Ruchi,

                   

                  Can't get my head around it. Would like to ask Simon Runc to the table..

                  Hi Simon Runc  could you "share";)  your thoughts on this one.

                  Thanks in advance

                   

                  Regard,

                  Norbert

                  • 6. Re: Selecting latest month value in a row
                    Ruchi Deshpande

                    Hi Norbert, 

                    I think I have figured it out. I will send you the workbook in a couple of hours.

                    Many thanks for your help!!

                     

                    Regards,Ruchi

                     

                     

                    Sent from my Samsung Galaxy smartphone.

                    • 7. Re: Selecting latest month value in a row
                      suresh.gooty

                      I think you should check some window_sum functions by checking previous values by specifying as 1,-1,-2 etc.,

                      • 8. Re: Selecting latest month value in a row
                        Simon Runc

                        hi Norbert,

                         

                        Looks like Ruchi has it sorted. My approach would have been to find the MAX Month (that it's NULL) for each Quarter/Child, and then use the figure for that Month, as the Quarter Figure (using LoDs, similar to what you have shown). Alternatively, as Suresh has said, we could use Table Calculations, but would mean having all dates in the VizLoD and then using some tricks (the IF FIRST()=0...END) to just bring back the required mark.

                         

                        I'm following the thread, so I'll see if Ruchi has any other issues.

                        • 9. Re: Selecting latest month value in a row
                          Ruchi Deshpande

                          Hey Suresh, Thanks for this but I tried it earlier and did not get the

                          desired output.

                           

                          Regards,

                          Ruchi

                           

                          On Wed, Dec 20, 2017 at 6:43 PM, suresh.gooty <tableaucommunity@tableau.com>

                          • 10. Re: Selecting latest month value in a row
                            Ruchi Deshpande

                            Hi Norbert and Simon,

                             

                            I used all the calculations as per Norbert and resolved the last part of

                            NULL in 'Display Grade First Quarter'  by applying a simple IF THEN

                            statement. The desired grades for 1st quarter is in

                            'Finaldisplay_first_quarter'.

                            Rest of the calculation is same.

                             

                            Attached is the worksheet. Thank you for all the help.

                             

                            Regards,

                            Ruchi

                             

                            On Thu, Dec 21, 2017 at 2:59 PM, Simon Runc <tableaucommunity@tableau.com>

                            • 11. Re: Selecting latest month value in a row
                              Norbert Maijoor

                              Hi Ruchi,

                               

                              That's GREAT! Have a nice X-mas:))

                               

                              Regards,

                              Norbert