4 Replies Latest reply on Jul 27, 2016 3:39 PM by emma.villarreal

    Help with lookup comparing dates

    emma.villarreal

      Hi I am new on this, I am using LOOKUP(([Clientes Nuevos]),12) to bring the last year orders but the problem is when I change the time from weeks to year or month so what can I do, I don't know how to use parameter or what other solution I have, I am attaching the sheet I am using

        • 1. Re: Help with lookup comparing dates
          Tom W

          The problem I see with your view is you are missing months i.e. June 2012. Thus your calc will be off. Click Analysis menu > Table Layout > Show Empty Rows.

           

          If you need to change the view to weeks, you should setup an alternative calculation which has a suitable offset for a 'weekly' aggregation.

           

          Also, you might be better just to use straight up native table calculations without creating specific calculated fields. See my implementation attached.

          • 2. Re: Help with lookup comparing dates
            emma.villarreal

            Thanks for ur answer, I know that june is missing but its just an example of what I have, Thanks for ur solution but it doent works for me because I need the quatity of order of last year and and the growing (new orders/last year orders), there must be an option to change without creating a a calculate field for a week, year or quarter

            • 3. Re: Help with lookup comparing dates
              Tom W

              You should be able to tweak my example to generate the output you want. I know the lookup formula for the previous year value is working correctly, I validated the data. Change the calculation for the % difference to suit the 'growth' formula you're trying to calculate.

               

              there must be an option to change without creating a a calculate field for a week, year or quarter

               

              The LOOKUP function relies on a parameter offset to know what to lookup. In your first example, this was set at 12 for 12 months. You could drive this with a parameter if you wanted. i.e. If parameter = 'week' then 52 else 12 end and then you would need to switch a parameter to toggle the different comparison.

              • 4. Re: Help with lookup comparing dates
                emma.villarreal

                Thanks did it know about that option!! thanks a lot