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

# Help with lookup comparing dates

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

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

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

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

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