11 Replies Latest reply on Feb 13, 2018 8:30 AM by Joe Oppelt

    Problem with Lookup Function

    Deepak Busanagari

      Lookup works only if the values are in the visualization. It is not working on filtered data. I need to calculate the growth rate from previous week. Can some one please help me

      PFA for sample workbook .

       

      Formula working on Sheet 6 but not on sheet 6(2).

       

      Jyothisree Rayagiri Sankarmagesh Rajan

        • 1. Re: Problem with Lookup Function
          Joe Oppelt

          Use a table calc to filter instead of a quick filter.

           

          See attached.


          The table calc doesn't remove rows from the underlying table, so the values are still out there to do your actual LOOKUP to get %diff.

          1 of 1 people found this helpful
          • 2. Re: Problem with Lookup Function
            Deepak Busanagari

            Hi Joe ,

             

            Thanks for your help. When I drop this caluclated field into filters I am getting this box (Range of values filter)

             

             

            How to solve this and select dates?? @ Joe Oppelt

            • 3. Re: Problem with Lookup Function
              Joe Oppelt

              Change it to discrete.  It's defaulting to continuous.  JUst click OK to on that RANGE box initially.  Then change the pill in the filter shelf to discrete.

              1 of 1 people found this helpful
              • 4. Re: Problem with Lookup Function
                Joe Oppelt

                I also had to change the display format of the calc.  By default it will be MM/DD/YYYY

                • 5. Re: Problem with Lookup Function
                  Deepak Busanagari

                   

                  This is what I am getting if I proceed with OK. Same is the case in your workbook , if I remove and add the filter once more. Please let me know what you did.

                  1 of 1 people found this helpful
                  • 6. Re: Problem with Lookup Function
                    Joe Oppelt

                    Right-Click on the pill in the filter shelf and select "Edit Table Calc"

                     

                     

                    then  switch the calc from "Table(across)" to "Table(down)".

                     

                    Let me know how that goes.

                    1 of 1 people found this helpful
                    • 7. Re: Problem with Lookup Function
                      Deepak Busanagari

                      Got it!! Thank you very much.

                      • 8. Re: Problem with Lookup Function
                        Deepak Busanagari

                        Hi Joe ,

                         

                        I am not able to apply this filter to other sheets. How to apply this filter to all the sheets on the dashboard. If i add them manually i 'll have to display each filter separately. Please let me know. Thank you.

                         

                        Please find the attached work book. Requirement is to select one weekdate and the dashboard numbers should change accordingly. Please help.

                         

                        All the % sheets are working only if the date is given "All"

                         

                        .

                         

                         

                        Jyothisree Rayagiri Sankarmagesh Rajan Lénaïc Riédinger Jen Shepherd Zhouyi Zhang

                         

                        Regards,

                        Deepak

                        • 9. Re: Problem with Lookup Function
                          Joe Oppelt

                          You can't "Apply to other sheets" with a table calc.  Table calcs are specific to the underlying table for the sheet where they are being used.

                           

                          You need to have a table calc filter for what you are doing though.  The operation you are doing requires a table operation to get previous data.  And the only way to display a part of the table and still have access to the hidden parts is to use a table calc to filter what gets displayed.

                           

                          Maybe you can change the approach to choosing what to display by letting the user select a start-date and end-date in two separate parameters.  Then you can have a table calc that does something like this:

                           

                          LOOKUP(  (IF attr([Date]) >= [Start Param] and attr([Date]) <= [End Param] then 1 else 0 END) , 0)

                           

                          Use that as a filter and select for 1.  You would need to add that calc to each sheet, but they all would be driven by the params, so you would only have to have the params displayed once.

                          • 10. Re: Problem with Lookup Function
                            Deepak Busanagari

                            Hi Joe ,

                             

                            Thank you once again for coming back. In the work sheet attached in my previous reply there is are some sheets like Net adds%(3 other) . I have used Actual Date filter and made it context to make the table calculated filter dependent on it. The filter is working only if I give "All" . But if I give any particular value in the filer , everything in the chart vanishes. I am not able to understand why this works like this.

                             

                            Forget about the expected result but this filter behavior is what worrying me much. Can you please explain me why this works like this.

                             

                             

                            Regards,

                            Deepak.

                            • 11. Re: Problem with Lookup Function
                              Joe Oppelt

                              YOUR {Date] filter gets only one mark.  No matter which date you select, it's returning only one week of data.


                              Your [Net Ads] calc looks up offset negative-1.  That means if you select 10/22/2017 in the filter, there is no 10/15/2017 data to look up.


                              That's why you need to have the table calc filter.  [Week_TC] tells the sheet what part of the underlying table to display, but the whole underlying table still exists for the sheet.  If you select only 10/22 in the table calc filter, it tells tableau to display the data for 10/22, but the lookup -1 still finds 10/15 in the underlying table and can calc the value you need for 10/22.

                               

                              Will your requirement need do display only one week at a time.  (I see that's how you've set up the [Date] filter.)  If so, you can have one parameter that displays all the weeks, and the user can select a week without having to specify a start and end date.  If you need to display multiple weeks, then the start-date and end-date suggestion I gave earlier is the way to handle that.