Sum Between Two Date Variable Fields

[#1]

Hi Experts

I have a problem hopefully you can fix.

I would like to calculate three expressions in a single pivot table the calculations are:

1. sum LineTotal between vROIStartDate and vROIEndDate - 3 month where RecSource = ‘Sales Invoice’
2. sum LineTotal between vROIStartDate and vROIEndDate where RecSource = ‘Sales Invoice’
3. sum LineTotal between vROIStartDate and vROIEndDate – 1 Year where RecSource = ‘Sales Invoice’

I have the following =Sum({1<CreateDate={'$(=DATE(vROIStartDate,'MM/DD/YYYY'))'}>} LineTotal) which calculates one date but cant figure out the rest of the of the calculation.

Would appreciate any help

[fosuzuki · Accepted solution]

Hi,

The trick with the dates is for example if you want to get the CreateDate between 01/01/2011 and 12/31/2011 use something like CreateDate={">=01/01/2011<=12/31/2011"}

try these:

1. Sum({<RecSource={Sales Invoice}, CreateDate={">=$(=DATE(vROIStartDate,'MM/DD/YYYY'))<=$(=addmonths(DATE(vROIEndDate,'MM/DD/YYYY'), -3))"}>} LineTotal)

2. Sum({<RecSource={Sales Invoice}, CreateDate={">=$(=DATE(vROIStartDate,'MM/DD/YYYY'))<=$(=DATE(vROIEndDate,'MM/DD/YYYY'))"}>} LineTotal)

3. Sum({<RecSource={Sales Invoice}, CreateDate={">=$(=DATE(vROIStartDate,'MM/DD/YYYY'))<=$(=addmonths(DATE(vROIEndDate,'MM/DD/YYYY'), -12))"}>} LineTotal)

Hope this helps you.

Regards,

Fernando

[perumal_41]

Hi

Use this Expression use ful for requirement .

1 . Sum({<RecSource={'Sales Invoice'}, CreateDate={">=$(=DATE(vROIStartDate,'MM/DD/YYYY'))<=$(=DATE(vROIEndDate-3,'MM/DD/YYYY'))"}>} LineTotal)

2 . Sum({<RecSource={'Sales Invoice'}, CreateDate={">=$(=DATE(vROIStartDate,'MM/DD/YYYY'))<=$(=DATE(vROIEndDate,'MM/DD/YYYY'))"}>} LineTotal)

3. Sum({<RecSource={'Sales Invoice'}, CreateDate={">=$(=DATE(vROIStartDate,'MM/DD/YYYY'))<=$(=DATE(vROIEndDate-1,'MM/DD/YYYY'))"}>} LineTotal)

[#4]

Fernando thanks a lot this works great