Max Date across multiple sources

[#1]

Hi All,

I have a table that pulls client training data from an Excel file that looks like the below:

ContactDate     UserId     UserName

01/03/2014       12345     John Smith

05/03/2014       56789     Bob Taylor

etc.

As well as this, I'm pulling operational usage data from SQL into a table like this:

UsageDate     UserId     UserName

13/04/14          12345     John Smith

14/04/14          12345     John Smith
01/06/14          56789     Bob Taylor

07/06/14          56789     Bob Taylor

What I'm trying to achieve is:

1) a field that returns the 'last used' date i.e. the MAX of UsageDate for each UserId.

2) a field that counts the number of instances used in the last 3 months

3) a field that counts the number of instances used 30 days prior to ContactDate (a field in the Excel)

I can't work out if the best approach is via the Script, an expression or a combination of the two!

Many thanks in advance for any help offered,

Al

P.S. Apologies if this is posted in the wrong place.  Mods - please feel free to move somewhere appropriate (first time posting).

[jerem1234 · Accepted solution]

You can do all that with expressions in the dashboard. If performance doesn't become a problem when you have the expressions in, then it won't be necessary to move to script. But it seems like expressions would be easiest to do, with the least amount of code.

For 1) try this expression in straight table with dimension of UserId (and/or UserName):

date(max(UsageDate))

For 2) try this expression in straight table with dimension of UserId (and/or UserName):

count({<UsageDate = {'>=$(=addmonths(today(), -3))'}>}UsageDate)

For 3) try this expression in straight table with dimension of UserId (and/or UserName):

count({<UsageDate = {'=UsageDate<ContactDate and UsageDate>ContactDate-30'}>}UsageDate)

(you can also use an if statement if you want for this expression)

Please find attached. If your data is linked similar to mine in your actual dashboard, then these expressions should work.

Hope this helps!

[#3]

Thanks for your help Jeremiah, that's fantastic!  One thing though, the second expression doesn't seem to work with my data; the first and third work great (and I've reconciled these manually through SQL) but the second only shows 0 for all counts even though it clearly states it was used yesterday in the date last used.  I tested the expression in your attachment (thanks again) and it works fine so not sure what's going wrong.  Could it be a formatting issue?

[jerem1234]

Try surrounding the addmonths with num like:

count({<UsageDate = {'>=$(=num(addmonths(today(), -4)))'}>}UsageDate)

and see if that works, else you might have to try formatting the format of the date that is returned from addmonths, try:

count({<UsageDate = {">=$(=date(addmonths(today(), -4), 'M/D/YYYY'))"}>}UsageDate)

where M/D/YYYY would be the format that the dates in your field UsageDate are in.

Hope this helps!

[#5]

Unfortunately I'm still getting 0 across the board there.  Would I need to specify the format in the script?  I'm not using preceding load for the SQL part of it if that helps?  Thanks in advance.

[jerem1234]

Look at what formats the dates in your field UsageDates are in. Then look at what format today() returns. If they don't match, then use whatever format Usage Dates are in for FORMAT:

count({<UsageDate = {">=$(=date(addmonths(today(), -4), 'FORMAT'))"}>}UsageDate)

If that doesn't work, check to make sure that the dates from UsageDates are actually dates (and not strings) by using a listbox and using num(UsageDates) as an expression and make sure they have numbers coming back.

Another neat trick, would be to create a straight table, then put the expression in, then see what the dollar-sign expansion in the set analysis is returning by looking at the label of the expression (make sure you didn't put a label in):

Hope this helps!

[#7]

Thanks Jeremiah, that's done the trick!  As one date is coming from SQL and the other from Excel the SQL one was actually a TimeStamp - reformatted that and all is working perfectly. Thanks again.