topic Re: Using rolling dates and setting them as variables in QlikView

Using rolling dates and setting them as variables

Tue, 13 Nov 2012 01:40:00 GMT

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 !!!

Re: Using rolling dates and setting them as variables

jonathandienst — Tue, 13 Nov 2012 06:01:55 GMT

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

Re: Using rolling dates and setting them as variables

Tue, 13 Nov 2012 06:12:29 GMT

I love you !

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

Re: Using rolling dates and setting them as variables

renjithpl — Tue, 13 Nov 2012 06:21:41 GMT

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

Re: Using rolling dates and setting them as variables

Tue, 13 Nov 2012 07:02:53 GMT

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 !

Re: Using rolling dates and setting them as variables

jonathandienst — Tue, 13 Nov 2012 07:09:21 GMT

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))

Re: Using rolling dates and setting them as variables

Tue, 13 Nov 2012 07:22:41 GMT

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

Re: Using rolling dates and setting them as variables

renjithpl — Tue, 13 Nov 2012 07:37:14 GMT

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

Re: Using rolling dates and setting them as variables

Tue, 13 Nov 2012 09:15:30 GMT

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

Re: Using rolling dates and setting them as variables

Wed, 14 Nov 2012 01:05:03 GMT

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 !

Re: Using rolling dates and setting them as variables

makkemik — Mon, 28 Apr 2014 09:22:43 GMT

Hello Tomas,

I need the expretion like this;

when I selected Year or (Year and month), I want to find last 365 days sales (from max selected value to 365 days before)

Could you please send an example or set analyze ?

kind regards

Murat