Date range across years

[#1]

Hello,

    I have a set of data, which is in between 01 jan 2013 to 31 dec 2015.

I made a pivot table with the totals per year, and so far so good.

Now I want to allow the user to reduce the subset at will, to make a comparison between years in a specificied time.

Example:

Entire data subset from "01 jan 2013" to "31 dec 2015"

YEAR - TOTAL

2013 - 100

2014 - 110

2015 - 20

Subset "at will" from "01 jan" to "28 feb"

YEAR - TOTAL

2013 - 16

2014 - 14

2015 - 15

How to do that?

I tried with this:

<CODE>

Sum({<DATE_FIELD = {">=$(=MakeDate(PIVOTED_YEAR_FIELD, Month(COMPARISON_FROM_DATE), Day(COMPARISON_FROM_DATE)))<=$(=MakeDate(PIVOTED_YEAR_FIELD, Month(COMPARISON_TO_DATE), Day(COMPARISON_TO_DATE)))"}>} (SUM_FIELD))

</CODE>

But something not work properly...

Any help, please?

Thanks

[pokassov]

Hi!

Sum({<PIVOTED_YEAR_FIELD=, COMPARISON_FROM_DATE=, COMPARISON_TO_DATE=, DATE_FIELD = {">=$(=MakeDate(min(PIVOTED_YEAR_FIELD), Month(COMPARISON_FROM_DATE), Day(COMPARISON_FROM_DATE)))<=$(=MakeDate(max(PIVOTED_YEAR_FIELD), Month(COMPARISON_TO_DATE), Day(COMPARISON_TO_DATE)))"}>} (SUM_FIELD))

[SunilChauhan]

Create Month from date field

and write simpley

sum({<Month_Field={'Jan',Feb,'Mar'} >}SumField)

add Remaining field which you require to remove selction with = sign before or aftre mOnth condition above

[#4]

Hi, and thanks.

It's equal to write: SUM(SUM_FIELD)

In my mind I figured something like:

SUM(DATE_FIELD >= (Makedate(EVALUATING_RECORD_YEAR, COMPARISON_START_MONTH, COMPARISON_START_DAY) <= Makedate(EVALUATING_RECORD_YEAR, COMPARISON_END_MONTH, COMPARISON_END_DAY)) (SUM_FIELD))

Where I'm wrong?

Thanks again

[#5]

Hi, and thanks.

Sorry, but I do not understand what you're saying..

[SunilChauhan]

Create Month from date field in script using

Month(Date_field) as Month and then use

sum({<PIVOTED_YEAR_FIELD=, COMPARISON_FROM_DATE=, COMPARISON_TO_DATE=,Month ={'Jan',Feb,'Mar'} >}SumField)

hope this helps

[#7 · Accepted solution]

Thaks all!

Solved with:

sum({<RECORD_DATE={">=$(=Makedate(PIVOTED_YEAR_FIELD, num(MONTH(COMPARISON_FROM_DATE)), DAY(COMPARISON_FROM_DATE) ))<=$(=Makedate(PIVOTED_YEAR_FIELD, num(MONTH(COMPARISON_TO_DATE)), DAY(COMPARISON_TO_DATE) ))"}>}

A simple date between, but the problem is with missing cast to NUM of MONTH functions.