sum MonthStartDate to selected date

[#1]

Hi,

I have a requirement where if user selects a date he should be able to see sum of sales from month start date to till selected date.

Ex:- If in filter user has selected 21-04-2015 then he should be able to see sales from 01-04-2014 to till date.

I tried  below expression but not getting desired result. Please suggest the solution.

sum({<TargetMonth=,TargetYear=,DivName=,KeyDescription=,

ProductName=,SubCatName=,ProductCategory=,ProductNo=,FiscalYear  ={$(vCurrentYear)},

SaleDate={">=$(=monthstart(GetFieldSelections(SaleDate)))"}

,

SaleDate={"<=$(=GetFieldSelections(SaleDate))"}

>}ActualQuantity)

Regards,

Navdeep

[ali_hijazi]

you need to ignore selection on SaleDate

so add the following to your set analysis SaleDate =

[datanibbler]

Hi Navdeep,

well, when the user has selected a date (and it's in proper date_format), you should be able to use the Monthstart() function on it to get that month's starting_date. Try that on its own first (good practice, always test the individual elements of a formula).

=> Then you can just use a formula, even without set_analysis, like

>> SUM(IF([sale_date] >= Monthstart[selected_date], [sales_amount], 0) <<

There won't be any sales for dates in the future, so you don't have to query that side.

HTH

P.S.: Set_analysis is a great tool and you can do cool things with it. But it's quite resource-intensive, so IMHO you should try to do without if possible.

[Anonymous · Accepted solution]

Hi,

Try

sum({<TargetMonth=,TargetYear=,DivName=,KeyDescription=,

ProductName=,SubCatName=,ProductCategory=,ProductNo=,FiscalYear  ={$(vCurrentYear)},

SaleDate={">=$(=monthstart(GetFieldSelections(SaleDate)))<=$(=GetFieldSelections(SaleDate))"}

>}ActualQuantity)

Regards,

Greeshma

[#5]

Thank you all for your valuable time.

This is the other option I tried and working

=sum({

<

....... SaleDate={">=$(=monthstart(GetFieldSelections(SaleDate)))"}

>

  *

<........SaleDate={"<=$(=GetFieldSelections(SaleDate))"}

>}ActualQuantity)