Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
boopeshj
Partner - Creator II
Partner - Creator II

Rangesum ignore filter when using a previous expression

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

 dimexp1exp2comments
Jan1515normal running sum of exp1
Feb621normal running sum of exp1
Mar1536normal running sum of exp1
Apr642normal running sum of exp1
May045running sum of exp1 + all other future months
Jun00 
Jul00 
Aug00 
Sep10 
Oct00 
Nov10 
Dec10 
Labels (2)
2 Solutions

Accepted Solutions
lironbaram
Partner - Master III
Partner - Master III

hi 

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 

View solution in original post

sunny_talwar

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))

 image.png

View solution in original post

5 Replies
lironbaram
Partner - Master III
Partner - Master III

hi 

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 

sunny_talwar

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))

 image.png

boopeshj
Partner - Creator II
Partner - Creator II
Author

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

sunny_talwar

Previous expression? Not sure I follow?

boopeshj
Partner - Creator II
Partner - Creator II
Author

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
))