topic Sum Between Two Date Variable Fields in QlikView

Sum Between Two Date Variable Fields

Tue, 13 Dec 2011 18:20:20 GMT

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:

sum LineTotal between vROIStartDate and vROIEndDate - 3 month where RecSource = ‘Sales Invoice’
sum LineTotal between vROIStartDate and vROIEndDate where RecSource = ‘Sales Invoice’
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

Sum Between Two Date Variable Fields

fosuzuki — Wed, 14 Dec 2011 10:52:43 GMT

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

Sum Between Two Date Variable Fields

perumal_41 — Wed, 14 Dec 2011 11:47:08 GMT

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)

Sum Between Two Date Variable Fields

Wed, 14 Dec 2011 13:11:43 GMT

Fernando thanks a lot this works great