Set analysis for date range

[Anonymous]

Hi,

I'm struggling to get a set analysis working when the selection requires a range of values (in my case dates). Essentially I'm trying to SUM the values in a field where the 'date' is between a start date and end date. If I try to do this for a single date (the maximum selected for the minimum selected then all works well, but I cannot get it working for a range of dates. I have looked at various posts on this and similar forums and there are lots of other people trying to do the same thing but I can't seem to get mine working.

Here is a sample of what I have:

- my date values range from 2014-09-02 to 2014-12-02

- during my load script I set a variable (vDate.Today) to be the maximum date value (so 2014-12-02)

- I'm then trying to SUM a SalesValue for all rows in the previous calendar month (i.e. 2014-11-01 to 2014-11-30)

What happens though is that the SUM is done for ALL rows <= 2014-11-30.

In my set analysis code, it looks like the ending date criteria works ok, but the starting date criteria does not.

This is my set analysis expression:

=Sum({$<DateFld={">=$(Date(AddMonths(vDate.Today,-1),'01/MM/YY'))"} , DateFld={"<$(=Date(vDate.Today,'01/MM/YY'))"}>} SalesValue)

The attached qvw document also includes a text box where I display the start and end date values as calculated above. Those look fine to me.

All help gratefully received.

Cheers,

Dave

[sunny_talwar]

I would try this:

=Sum({$<DateFld={">=$(Date(AddMonths(vDate.Today,-1),'DD/MM/YY'))<$(=Date(vDate.Today,'DD/MM/YY'))"}>} SalesValue)

=Sum({$<DateFld={">=$(=Date(MonthStart(vDate.Today,-1),'DD/MM/YYYY'))<$(=Date(MonthEnd(vDate.Today, -1),'DD/MM/YYYY'))"}>} SalesValue)

Update: Sample attached

[maheshkuttappa · Accepted solution]

As I Understand you need to get Nov total that is 112

use below expression

=Sum({$<DateFld={">=$(=Date(AddMonths(vDate.Today,-1),'01/MM/YY')) <$(=Date(vDate.Today,'01/MM/YY'))"}>} SalesValue)

[Anonymous]

Hi Sunny,

Many thanks for that, I got it working with one (very) minor change).

You had coded:

=Sum({$<DateFld={">=$(=Date(MonthStart(vDate.Today,-1),'DD/MM/YYYY'))<$(=Date(MonthEnd(vDate.Today, -1),'DD/MM/YYYY'))"}>} SalesValue)

The second value is tested using "<", which (of course) would exclude any data for the last day of the month. I need to include data for that date, so I just changed "<" to "<=". (as I said, a minor change).

Cheers,

Dave

[Anonymous]

Hi Mahesh,

Many thanks - works a treat.

Cheers,

Dave