8 Replies Latest reply: Sep 28, 2016 2:35 AM by gidon david

Get data between dates

Hi Guys

I need your help to solve the customer request .

enclosed is a file with data of :

DOCDATE  date of transaction

QUANT qty

WARHS  no   ,

TYPE of transaction   .

and more

I need to create SUM of QUANT  ( per WARHS  from today to  date1 - that will be given as variable   - for ex 01/09/2015

I need to create SUM  of QUANT  ( per WARHS  from today to  date2 - that will be given as variable  - for ex 15/09/2015

and sum of QUANT between date1 to date2  where TYPE = 'M'

have a nice day .

gidon

• Re: Get data between dates

I was not clear on the out put that you need but you can take a look and the below and see if this is what you want.. If not can you please share your expected out put...

Or may be this:

• Re: Get data between dates

Hi Thirumala

I think there is one problem

SUM FOR DATE 1 is Ok

SUM FOR DATE 2 if Ok

BUT SUM BETWEEN DATES  IS WRONG  I THINK

thanks

gidon

• Re: Get data between dates

Gidon David wrote:

Hi Thirumala

I think there is one problem

SUM FOR DATE 1 is Ok

SUM FOR DATE 2 if Ok

BUT SUM BETWEEN DATES  IS WRONG  I THINK

thanks

gidon

Then try this for diff

Interval(Sum([DATE 1]) - Sum([DATE 2]))

• Re: Get data between dates

A small correction would be required in the expression. Try like:

sum({<DOCDATE={'>=\$(=date(vdate1)) <=\$(=date(vdate2))'},TYPE={'M'}>} QUANT)

• Re: Get data between dates

Can you try the below expression as tresesco suggested for the between dates:

sum({<DOCDATE={'>=\$(=date(vdate1)) <=\$(=date(vdate2))'},TYPE={'M'}>} QUANT)

If that is not correct, can you share the output....

• Re: Get data between dates

Hi guys

thanks for the help

it is correct answer , that is the way to do it

gidon

• Re: Get data between dates

try like this?

1) sum({<DateField={'>=\$(=Date(Date1))<=\$(date(today()))'}>} QUANT)

2) sum({<DateField={'>=\$(=Date(Date2))<=\$(date(today()))'}>} QUANT)

3) 1) sum({<Type={'M'},DateField={'>=\$(=Date(Date1))<=\$(date(Date2))'}>} QUANT)

make sure format of all date fields value should be same.

• Re: Get data between dates

HI,

Please Check it below attached file.