Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
Or this
Count(DISTINCT {< [Project] ={'Project 1','Project 2'}, LastSold = {"=LastSold >= Today() - 30"} >} InvoiceNumber)
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)
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.
Try this:
Count(DISTINCT {<[Project] = {'Project 1','Project 2'}, LastSold = {"$(='>=' & TimeStamp(Today() - 30, 'MM/DD/YYYY hh:mm:ss'))"}>} InvoiceNumber)
Or this
Count(DISTINCT {< [Project] ={'Project 1','Project 2'}, LastSold = {"=LastSold >= Today() - 30"} >} InvoiceNumber)
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.
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