Passing user selected criteria from listboxes into expressions in other sheet objects

[#1]

Hi everyone, I am hoping someone may be able to help me with this?

I have created a qvw file to analyse vehicle data. I have also created a calendar table to allow the user to select timeframes they are interested in when analysing the data.

The problem I have is there is no direct link between the vehicle table and the calendar table.

For example if a user wants to look at vehicles owned in 2008 I need to say something like - if DatePurchased < Jan 2008 and DateSold is > Dec 2008

To do this I need to pass the date range selected by the user in the list boxes into the expression. Is this possible?

It then needs to update when the user further selects dates. I.e. A list of years and months in list boxes across the top and a table box below showing vehicles data for all vehicles owned in the time frame selected. I.e. 2008 that then further selects if the user selects January.

I also then want to create further gauges and charts based on this information such as vehicle turnover for the dates selected.

The only way I have found any success so far is to create a table on load that links to the calendar table listing all vehicles owned on each day. I.e.:

01/01/2000 Veh a
01/01/2000 Veh b
01/01/2000 Veh c
02/01/2000 Veh a
02/01/2000 Veh b
etc...

This table is already up to 500k rows and requires me to use complicated Aggr functions to total the figures correctly and seems ungainly.

I also need to do something similar with employees.

So is it possible to pass values selected from list boxes into expressions in other sheet objects?

Any ideas/examples greatly appreciated!

Thanks

[#2 · Accepted solution]

Hi

It's certainly possible to use selected field values in an expression, and it can be done in a few ways, for the purposes of this example, let's assume that you have a list box that allows the user to select a year number, and the year number is a field called YearNum in a table. Let's then assume that you want to look at the value of purchase price (field called PurchasePrice) for those cars purchased in that year.

Your expression could look like: =IF(Year(PurchaseDate)=GetFieldSelections(YearNum),Sum(PurchasePrice))

This type of expression can be used in tables, charts, basically everything you can create an expression in.... so you certainly don't need to have your extended link table.

If you have more specific questions, please just ask, I'll try to help as much as I can.

Regards,

[#3]

Thanks Nigel. That has got me started nicely and I can now do my calculations by year.

I also have a month list and a quarter list to incorporate if possible. I think the month should be reasonably straight forward to concatenate with year but am thinking the quarter one may be difficult? Also there could be multiple selections in the list boxes.

Automatically I am thinking of some sort of loop that goes through the results of a getcurrentselections() statement. But I think I am right to think that it may not be possible to do that within an expression and also variables cannot be set in expressions?

Would you mind telling me if you think it is possible and what direction you would go in attempting to solve this?

Many thanks again for your time

[#4]

Hi

The month list should be no problem, I assume the month list is separate to the year list and therefore the expression would simply include an AND statement such as IF(Year(PurchaseDate) = GetFieldSelections(YearNum) , Sum(xxxxx) ).

As for multiples, you should try the solution you have right now, the function call GetFieldSelections means exactly that, it should match against any and all selections!! No special coding required for that.

Regards,

[#5]

Hi

Sorry, just read my reply and realised the example expression makes no bloody sense at all, I'm at the end of a very long day (that's my excuse anyway).

Expression should be something like:

IF(Year(PurchaseDate) = GetFieldSelections(YearNum) AND Month(PurchaseDate) = GetFieldSelections(MonthNum), Sum(xxxxx) ).

Sorry about that,