topic Re: Rangesum ignore filter when using a previous expression in QlikView

Rangesum ignore filter when using a previous expression

boopeshj — Sat, 16 Nov 2024 18:35:04 GMT

I have 1 dimension and 2 expressions.

Exp2 is just a running sum of exp1.

Exp1: some calculations - COUNT({<Ignore_filter=>}) all fine. works as expected. Filter is ignored.

Exp2: IF([Dim]<MONTH(TODAY()),RANGESUM(ABOVE([exp1],0,RowNo())),
IF([Dim]=MONTH(TODAY()),RANGESUM(ABOVE([exp1],0,RowNo()))+RANGESUM(BELOW([exp1],1,12-RowNo())),0))

When trying to use SUM to ignore filter on Exp1 like RANGESUM(ABOVE(SUM({<Ignore_filter=>}[exp1]),0,RowNo()) get an error on expression.

Any leads much appreciated

dim	exp1	exp2	comments
Jan	15	15	normal running sum of exp1
Feb	6	21	normal running sum of exp1
Mar	15	36	normal running sum of exp1
Apr	6	42	normal running sum of exp1
May	0	45	running sum of exp1 + all other future months
Jun	0	0
Jul	0	0
Aug	0	0
Sep	1	0
Oct	0	0
Nov	1	0
Dec	1	0

Re: Rangesum ignore filter when using a previous expression

lironbaram — Wed, 27 May 2020 10:34:06 GMT

this should work for you

if(only({<[ dim]>} [ dim])<MonthStart(today()), RangeSum(Above( (　Sum({<[ dim]>}exp1)　) , 0, rowno())), if(only({<[ dim]>} [ dim])= monthstart(TODAY()),RangeSum(Above( (　Sum({<[ dim]>}exp1)　) , 0, rowno()))+RANGESUM(BELOW(Sum({<[ dim]>}exp1),1,12-RowNo())),0))

in expression i prefer to use monthstart format when comparing dates

Re: Rangesum ignore filter when using a previous expression

sunny_talwar — Wed, 27 May 2020 11:27:21 GMT

You can try something like this

If(Month < Month(Today(1)), RangeSum(Above(Sum(Sales), 0, RowNo())), If(Month = Month(Today(1)), Sum(TOTAL Sales), 0))

Re: Rangesum ignore filter when using a previous expression

boopeshj — Thu, 28 May 2020 09:05:48 GMT

@sunny_talwar Can sales here be previous expression?
the Expression1 is very complex.

Re: Rangesum ignore filter when using a previous expression

sunny_talwar — Thu, 28 May 2020 11:20:44 GMT

Previous expression? Not sure I follow?

Re: Rangesum ignore filter when using a previous expression

boopeshj — Mon, 17 Aug 2020 21:33:41 GMT

I worked it out something like this. Along with some inputs from @sunny_talwar & @lironbaram .

Thanks guys.

IF([Month]=MONTH(TODAY()),[expr1],
IF([Month]>MONTH(TODAY()),ABOVE([expr2])+[expr2],0
))