Set Analysis - Dates greater than a given number of days ago

[#1]

Hi,

I'm running into a problem creating a set analysis using Today() and a user-specified variable.  I'd like the user to be able to interactively set a number of days and have the total cost of sales between that many days ago and today be summed i.e. sum all the sales from 10 days ago up until now.  So far I've come up with the following expression but it always yields zero:

=Sum({$<SalesDate={">=$(Date(Today()-$(vDays)))"}>}Cost)

Can anyone help point out the error in my expression?

Thanks,

Rav

[Sokkorn · Accepted solution]

Hi Rav,

I would suggest you to create a new field for set analysis (easy to control). Something like

[Table]:

LOAD

...

Num(SalesDate)    AS [SalesDateNum],

...

Then use =Sum({$<SalesDateNum = {">=$(=Today()-vDays)"}>} Cost) in your Text Object.

Regards,

Sokkorn

[daveamz]

Hi Rav,

Sokkorn is right, you have to create a new field where the date will be in numeric format. For your data model I suggest to use: num(date#(Date,'M/D/YYYY h:mm')) as SalesDate, since your data format is not in the standard 'M/D/YYYY' or 'M/D/YYYY h:mm:ss[.fff] TT' format.

Then create another variable like vIniatialDate =today()-$(vDays) (easyer to update) then:

=Sum({<SalesDate={">=$(vInitialDate)"}>}Cost)

Regards,

David

[#4]

Excellent, thank you Sokkorn!

-Rav

[#5]

Thank you Dave for the additional advice.  Adding the variable for the initial date helps keep the set expression more readable.  There was a typo in the code example that defines the variable (just in case anyone ever tries copying and pasting from this thread):

vInitialDate =today()-$(vDays)

-Rav