topic Re: YTD in QlikView

YTD

Thu, 15 Oct 2015 11:26:13 GMT

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!

Re: YTD

Gysbert_Wassenaar — Thu, 15 Oct 2015 11:46:02 GMT

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

Re: YTD

Thu, 15 Oct 2015 11:55:48 GMT

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

Re: YTD

Gysbert_Wassenaar — Thu, 15 Oct 2015 16:22:23 GMT

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)