Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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
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)
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
Hi Mahesh,
Many thanks - works a treat.
Cheers,
Dave