Cumulated sum (loops in expression?)

Hi all,

So I'm wondering if its possible to do a loop within an expression in QlikSense.

I've got income data across 2 years, and want to do YTD income comparisons - so if i'm in March 2017, for 2016, I need to add up Jan/Feb/March but ignore the rest of the year..

I can do this with a lengthy set of IF statements: (vMonth & vYear are user controllable variables from the report)

eg:

---------------------------------------------------------------------

//January

if($(vMonth)>= 1,

//In Month

Sum(if(monthstart(cashbookdate) = Makedate($(vYear)-1,1)

  and (EffectiveDate <=  MonthEnd(MakeDate($(vYear)-1,1)) or (isnull(EffectiveDate) and year(cashbookdate) = $(vYear)-1))

        and TranType = 'New Business'

    ,   (Commission_Calcd)))

+

//Brought forward

  Sum(if(cashbookdate < MakeDate($(vYear)-1, 1)

  and MonthStart(EffectiveDate) = MakeDate($(vYear)-1, 1)

       and TranType = 'New Business'

    ,   (Commission_Calcd)))

,0)

+

//February

if($(vMonth)>= 2,

//In Month

Sum(if(monthstart(cashbookdate) = Makedate($(vYear)-1,2)

  and (EffectiveDate <=  MonthEnd(MakeDate($(vYear)-1,2)) or (isnull(EffectiveDate) and year(cashbookdate) = $(vYear)-1))

        and TranType = 'New Business'

    ,   (Commission_Calcd)))

+

//Brought forward

  Sum(if(cashbookdate < MakeDate($(vYear)-1, 2)

  and MonthStart(EffectiveDate) = MakeDate($(vYear)-1, 2)

       and TranType = 'New Business'

    ,   (Commission_Calcd)))

,0)

+

//March

---------------------------------------------------------------------

//Continued for the rest of the months..

but this takes a very long time for Qlik to calculate - and also, when trying to do YOY % this means 2 lots of that code (current year / prior year) which doesnt actually fit in the allowed space in QlikSense as a formula.

I was hoping to do something like:

---------------------------------------------------------------------

Set i = 1;

do while $(vMonth) >= i

Sum(if(monthstart(cashbookdate) = Makedate($(vYear),i)

  and (EffectiveDate <=  MonthEnd(MakeDate($(vYear),i)) or (isnull(EffectiveDate) and year(cashbookdate) = $(vYear)))

        and TranType = 'New Business'

    ,   (Commission_Calcd)))

+

//Brought forward

  Sum(if(cashbookdate < MakeDate($(vYear), i)

  and MonthStart(EffectiveDate) = MakeDate($(vYear), i)

       and TranType = 'New Business'

    ,   (Commission_Calcd)))

Let i = i + 1;

loop

---------------------------------------------------------------------

but Sense doesnt seem to like loops in the expressions? At least, not how I've typed it there anyway.

Does anyone know of any way I could add up the calculations above in as little code as possible to get away from the list of IF statements?

Thanks,

Alec