Using rolling dates and setting them as variables

[#1]

Hello !

I'm trying to figure out the sales for each of my clients in the last 90, 180, 365 days. I'd like to store the sales in those time-frames as variables.

I was trying something like: sum($<{Orderdate >= today()-365, Orderdate <= today()} $(vSales)) but to no avail...

Thanks a lot in advance !!!

[jonathandienst]

Hi

This is the correct syntax for your statement:

sum({<Orderdate = {">=$(=today()-365) <=$(=today())"}>} $(vSales))

Note that OrderDate must be in the same format as your default format (as set in the environment Set statements at the top of your load script), and the expression in $(vSales) must not contain an aggregate function (such as Max or Sum)

Hope that helps

Jonathan

[#3]

I love you !

On Tue, Nov 13, 2012 at 3:02 PM, Jonathan Dienst

[renjithpl]

sum ({<Orderdate={">=$(=date(only(today())-365,'M/D/YYYY')) <$(=date(only(today()),'M/D/YYYY'))"}>} $(vSales))

[#5]

Another question as a follow-up.

I want to try to replace today() but a variable that is set on a fixed date

(let's say 2012/10/31). I've set up vDate = 2012/10/31 and replace today()

in the formula you gave me with $(vDate) but it doesn't seem to work...

Many thanks !

[jonathandienst]

Is your default date format YYYY/MM/DD?

You can also use this:

Let vDate = Date#('2012/10/31');

Then the expression:

=sum({<Orderdate = {">=$(=Date(vDate-365)) <=$(Date(vDate))"}>} $(vSales))

[#7]

My date format is supposed:

SET DateFormat='YYYY/MM/DD';

I've set up vDate as Date#('2012/10/31')

Then I entered sum({} $(vSales)) but I only get zeros...

On Tue, Nov 13, 2012 at 4:09 PM, Jonathan Dienst

[renjithpl]

Try your expression in a pivot table without dimension, and bring the cursor on top of your expression, whether it gives the correct dates in needed format,

I think there is a format issue with OrderDate and the Variable Date,

if you correct it, it should work.

vDate = DATE(MakeDate(2012,10, 31), 'YYYY/MM/DD')

sum({<OrderDate={">=$(=date(vDATE-365,'YYYY/MM/DD')) <$(=date(vDATE,'YYYY/MM/DD'))"}>} $(vSales))

Regards

Ren

[#9]

Seems to be working ! Thanks a lot, that's really awesome !

[#10]

I have an additional question... I want to group value in a certain way:

for example, if the the month of first purchase was less than 1 month ago

(this is stored inside vSincenew), then I was to have the data name "New

customer", if vSincenew < 3, "Acquired > 3 months ago" etc...

Is there an easy way to do this ?

Thanks for your help !