Cumulated sum (loops in expression?)
Alec Brown Feb 21, 2017 6:54 AMHi 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