Comparing Dates

[#1]

I have a request from a customer to be able to compare sales data from a given time period. They want to be able to compare any particular date chosen from 2 different list boxes. What I have done is put the data in 2 different tables and added 2 seperate list boxes to the sheet. I have loaded 2 different date fields into the document. Although they contain the same values the field names are different. How do I get 1 table to display the values chosen from 1 listbox and the other table to display the values chosen from the second list box?

[#2]

Not sure if I need to post the excel spreadsheet or nit, but if I do let me know.

[#3]

Something like this

[#4]

Nick, Thanks for responding, but that doesn't fix my problem. I was able to get the expression to work that you gave me. The problem is the user wants to see 2 grids on the same sheet at once and compare the data in both grids relative to what is selected for the given list boxes. The sample that you sent me only has 1 set of list boxes and 1 grid. Thanks

[#5]

I've showed to you an approach, layout is your responsibility.

Clone the table, remove last two expressions from original table and first two expressions from cloned table.

[#6]

One of us is obviously confused about what is trying to be accomplished and how to do this. Here is a detailed example.

I have 4 List boxes and 2 pivot tables on 1 sheet.

2 list boxes will contain years(Call these Year1 & Year2) the other 2 listboxes will contain Months(Jan - Dec)(Call these Month1 & Month2)

2 pivot tables(Call these Table1 & Table2) will have the same layout and fields.

There may be 10 years of data or there may only be 2 years of data that is loaded from 1 database table. The database table contains a date field(TransactionDate). The user wants to be able to select a year and month from the first set of listboxes(Year1 & Month1) and then 1 pivot table(Table1) would show the data from that period selected.

Now we move on to the second set of listboxes and pivot table. The user wants to be able to select a year and month from second set of listboxes(Year2 & Month2) and then the second pivot table(Table2) would show the data from the period selected. All while the first set of listboxes and pivot table would stay the same. That way the user can compare the data between the 2.

I understand if I clone the table and add the expressions that would work, but the expression will only show data either from 2009 or 2010 and I need it to show the data from a time period that the user chooses. That is my problem.

How do I make it so that the user can choose from a set of listboxes or some other selection criteria the time frame they want to compare? Not a static time comparison. They want to be able to dynamically choose any 2 dates and do a comparison. They may want to compare 01/2010 to 03/2010 or 12/2009 to 01/2010 or 06/2009 to 06/2010 so on and so fourth.

Thanks in advance

[#7]

See attachment. As I told you, the idea is the same, the implementation is up to you. You can replace sliders by calendars objects but the expressions will stay the same. If you change a structure you will need to change Set Expressions only. And the idea will remain the same.

[#8]

Thanks Nick. That is exactly what I was looking for and I think I like the sliders better than the listboxes. Thanks again.

[#9]

I created an app that allow user to select any two years and then show the values(Quantity) with change and % change

I set up variables to keep track of what the user selects then Set Analysis for the expressions.

This allows the user to select any 2 years and it will compare the two.

Variables:

VLowYear = if(getselectedcount([GL PERIOD YEAR])=0,max([GL PERIOD YEAR])-1,left(getfieldselections([GL PERIOD YEAR]),4))

VhighYear = if(getselectedcount([GL PERIOD YEAR])=0,max([GL PERIOD YEAR]),right(getfieldselections([GL PERIOD YEAR]),4))

VCurrPeriod = right(Max([GL PERIOD]),2)

VhighYearDay = max([MOVEMENT DATE#])

VlowYearDay = MakeDate( VlowYear, VCurrPeriod,Day(max([MOVEMENT DATE#])))

Expression in Chart:

Label =VlowYear

Expression SUM({<[MOVEMENT DATE#] = {'<=$(VhighYearDay)'} >} [QUANTITY])

Label =VHighYear

Expression SUM({<[MOVEMENT DATE#] = {'<=$(VlowYearDay)'} >} [QUANTITY])

Label Quantity Change

Expression (column(2)-Column(1))

Label % change

Expression (column(2)-Column(1))/Column(1)

Let me know if this makes sense - if not we can hook up on a webex and I will be happy to show the app