YTD

[#1]

Hi all,

Im trying to improve the following expression.

This expression returns the sum of 'number of orders' for the selected year for all months until and including the selected month.

Can I do this in a better more efficient way?

sum (
{<Jaar ={'$(=GetFieldSelections(Jaar))'},
DIM_Budget_Item.Budget_Item_Omschrijving={'schoenen'},
Orders.Order_Type={'verkoop'},Orders.Bedrag_Exclusief={"<0>0"}, Maandnr=
>}
if (Maandnr <= GetFieldSelections(Maandnr),
Orders.Aantal_Artikelen))

Thanks in advance!

[Gysbert_Wassenaar]

Usually there's a date field available as well and then you can use something like MyDate={">=$(=YearStart(Max(MyDate)))<=$(=max(MyDate))"}

[#3]

How does this work? How would you apply this to the given expression?

[Gysbert_Wassenaar]

MyDate={'>=$(=YearStart(Max(MyDate)))<=$(=max(MyDate))'} is a set modifier that filters the values in the MyDate field. The current selections in other field result in a set of possible MyDate values. If you select a year and a month then Max(MyDate) will return the date value that's the maximum value in the select month of the selected year. This value is then use in the set modifier to select the date values in the MyDate value that lie between the start of the selected year and the maximum date in the select month.

You can use it like this:

sum(

     {< Maandnr=,

         MyDate={'>=$(=YearStart(Max(MyDate)))<=$(=max(MyDate))'},

         DIM_Budget_Item.Budget_Item_Omschrijving={'schoenen'},

         Orders.Order_Type={'verkoop'},

         Orders.Bedrag_Exclusief={"<>0"},

     >}

     Orders.Aantal_Artikelen)