Expression to include only the last 30 days' occurences

[dawgfather]

I have an expression that works inside a chart to limit to 2 projects. If I manually highlight only the dates in a field selection for the last 30 days, I get what I need.

How can I add another filter to the expression to count only the instances in the past 30 days?

My current expression is:

Count(DISTINCT {<[Project] ={'Project 1','Project 2'} >} InvoiceNumber)

I have a field named "LastSold" that has data like the below:

11/11/2016 14:47:23

12/03/2016 11:14:45

12/06/2016 13:42:13

01/03/2017 18:56:10

01/28/2017 14:48:40

02/01/2017 18:16:57

02/05/2017 20:15:03

02/17/2017 07:07:38

If the expression works, I'd expect that only the 01/28, 02/01, 02/05 and 02/17 instances would appear (since today is 2/27).

[vishsaggi]

May be this?

Count(DISTINCT {< [Project] ={'Project 1','Project 2'}, LastSold = {"= $(>=Timestamp(Max(LastSold) - 30, 'MM/DD/YYYY hh:mm:ss') < Timestamp(Now(), 'MM/DD/YYYY hh:mm:ss'))"} >} InvoiceNumber)

[dawgfather]

That seems like it would work, but it's not for me.

If I substitute in "[LastSold] = {'02/17/2017 07:07:38'} " for your "LastSold = {"= $(>=Timestamp(Max(LastSold) - 30, 'MM/DD/YYYY hh:mm:ss') < Timestamp(Now(), 'MM/DD/YYYY hh:mm:ss'))"} " I get a chart with that one instance - it's a start.

But if I put in your filter, I get "No data to display".

I tried just using a modification without the MAX piece... "[LastSold] = {"= $(>=Timestamp(Now()-30, 'MM/DD/YYYY hh:mm:ss') < Timestamp(Now(), 'MM/DD/YYYY hh:mm:ss'))"} and still no data. Tried it with Today() instead of Now() - still no data.

[sunny_talwar]

Try this:

Count(DISTINCT {<[Project] = {'Project 1','Project 2'}, LastSold = {"$(='>=' & TimeStamp(Today() - 30, 'MM/DD/YYYY hh:mm:ss'))"}>} InvoiceNumber)

[sunny_talwar · Accepted solution]

Or this

Count(DISTINCT {< [Project] ={'Project 1','Project 2'}, LastSold = {"=LastSold >= Today() - 30"} >} InvoiceNumber)

[dawgfather]

I started with Sunny's more simple example but still had issues. The Today() function returns a date only and all the timestamp functions never seemed to work, so I created a LastSoldDateOnly during the load and then used that in place of the LastSold and it worked.

DATE(DATE#(LastSold,MM/DD/YYYY hh:mm:ss'),'MM/DD/YYYY') as LastSoldDateOnly

Then used the modified formula of:

Count(DISTINCT {< [Project] ={'Project 1','Project 2'}, LastSoldDateOnly = {"=LastSoldDateOnly >= Today() - 30"} >} InvoiceNumber)

THAT finally worked.

[sunny_talwar]

This may look like a date, but it is in fact a timestamp. In order to get rid of time component, use Floor() function

DATE(Floor(DATE#(LastSold,MM/DD/YYYY hh:mm:ss')),'MM/DD/YYYY') as LastSoldDateOnly