Calculation of Total Sum Between two Date Range

[KKumar92]

Hi All,

I am working on a report, where I want to calculate the Sum of Quantity for a given date range. And I have used the following expression for the measure.

Sum({$<DATE = {'>=$(=$(vMiDate))<=$(=$(vPrDate))'}>}QTY_ONHAND)

However, I am getting the answer as zero. Please advise how this could be rectified.

Variables;

vMiDate = Min(Total DATE)

vPrDate = Date((Max(DATE) - 1), 'DD/MM/YYYY')

Expected Output;

DATE	QTY	vMiDate	vPrDate	SUM
31/01/2021	100	31/01/2021	30/01/2021	100
1/2/2021	25	31/01/2021	31/01/2021	100
2/2/2021	50	31/01/2021	1/2/2021	125
3/2/2021	25	31/01/2021	2/2/2021	175

[agigliotti]

Hi @KKumar92 ,

Maybe this:

=Sum( {< DATE = {">=$(vMiDate)<=$(vPrDate)"} >} QTY_ONHAND )

I hope it can help.

Best Regards

[KKumar92]

Hi Agigliotti,

I tried the above formula, but again I am getting the answer "0" for the sum.

Thanks.

[agigliotti]

Hi,

May be there are problems with vMiDate and/or vPrDate date formatting...
What do you get with the below expression:
=Sum( {< DATE = {">=31/01/2021<=02/02/2021"} >} QTY_ONHAND )

?

[KKumar92]

This expression return value 0.

Please check below for my variable declaration;

vMiDate = Min(Total DATE)

vPrDate = Date((Max(DATE) - 1), 'DD/MM/YYYY')

[agigliotti]

Let's put both variables in a text box to check if they are correctly formatted.
Also check your DATE field type and format.
What do you get?

[KKumar92]

Please check above for the output.

[MayilVahanan · Accepted solution]

Hi @KKumar92 

When u are using Date field as Dimension, ur expression gives zero, because the date range is less than the date values. 

In order to achieve the expected result, might be try like below

If(RowNo(TOTAL)=1, Sum(QTY), RangeSum(Above(Sum(QTY), 0, RowNo(TOTAL)))-Sum(QTY))

[KKumar92]

Hi MayilVahanan,

Thanks for the solution above, I am getting the values now.

If I rephrase the above question is following way,

Where I want to calculate the Sum Between vMiDate and vPrDate, also I want to add the stock value of Current Date (Max Date).

Example, to calculate the stock  at '2/2/2021', I will take summation of following variables;

vMiDate  = 31/01/2021

vPrDate = 1/2/2021

vMaxDate = 2/2/2021

Please advise how this could be achieved through the formula you have stated above.

Thanks

[MayilVahanan]

HI @KKumar92 

You can add set analysis (date filter) in the Sum(QTY) formula in order to achieve the result.

Make sure, all the date & variables are in same format.