Complex Expression SetUp

[#1]

Hi All,

I have a bit of a challenging situation which I thought I would ask some of the more experienced app developers for some assistance.

I have a spreadsheet which contains some odometer readings. I have created a dummy version of the data which I have attached as it may be a bit hard to explain in just words. I am trying to set up an app that analyses mileage, i.e. mileage today - mileage yesterday by field staff, as well as mileage between certain periods eg between July & Sept if I selected these in a list box.

The challenge comes in when you don't have an odometer reading for a particular day, mileage today - mileage yesterday no longer works. Also there is an aggregation problem if you ever have a situation when you don't have an odometer reading on the last or first day of the month.

Not sure how to get round this, but was thinking if there was a way to insure that there is a value for each employee for the odometer reading, by converting every blank to the last record reading for that employee (date wise) in an upload expression, or can this be handled some how when doing the actual expression for an object?

Any thoughts or guidance would be greatly appreciated.

Thank you

Herbiec09

[Nicole-Smith]

Check out the alt function:

alt(case1[ , case2 , case3 , ...] , else)

The alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used.

Example:

alt( date#( dat , 'YYYY/MM/DD' ),

date#( dat , 'MM/DD/YYYY' ),

date#( dat , 'MM/DD/YY' ),

'No valid date' )

Will test if the field date contains a date according to any of the three specified date formats. If so, it will return the original string and a valid number representation of a date. If no match is found, the text 'No valid date' will be returned (without any valid number representation).

I'm thinking in your case, you could use something like:

alt(YourTodayMileageExpression, 0) - alt(YourYesterdayMileageExpression, 0)

If this doesn't work and you need more help, you'll need to post a .qvw so we can see what your expression is:

Preparing examples for Upload - Reduction and Data Scrambling