topic Filter on above function in QlikView

Filter on above function

Thu, 03 Aug 2017 08:57:30 GMT

Hi everyone,

I'm trying to calculate a sum of past 6 values in a graph.

Consider this is my data:

LOAD * INLINE [

Year, Month, Value

2016,201601,20

2016,201602,14

2016,201603,15

2016,201604,17

2016,201605,23

2016,201606,18

2016,201607,10

2016,201608,34

2016,201609,29

2016,201610,17

2016,201611,19

2016,201612,24

2017,201701,28

2017,201702,19

2017,201703,20

2017,201704,15

2017,201705,18

2017,201706,28

2017,201707,21

];

And the calculation with the functions above and rangesum :

Rangesum(above(sum(Value),0,6))

Month	sum(Value)	Rangesum(above(sum(Value),0,6))
201601	20	20
201602	14	34
201603	15	49
201604	17	66
201605	23	89
201606	18	107
201607	10	97
201608	34	117
201609	29	131
201610	17	131
201611	19	127
201612	24	133
201701	28	151
201702	19	136
201703	20	127
201704	15	125
201705	18	124
201706	28	128
201707	21	121

For 201707 the calculation is : 21+28+18+15+20+19 = 121.

My problem is i want to show in a line graph only the 2017 year, so i've changed my calculation by:

Rangesum(above(sum({<Year ={"2017"}>}Value),0,6))

But the filter on 2017 exclude my 2016 datas in the calculation so the firsts 6 months are wrong.

Do you have a solution to display only the 2017 year in the graph without filter the expression ?

Please find the qvd in attached.

Thanks

Re: Filter on above function

krishnacbe — Thu, 03 Aug 2017 15:04:36 GMT

Try below Expression

if(Year='2017', Rangesum(above(sum(Value),0,6)))

Re: Filter on above function

sunny_talwar — Thu, 03 Aug 2017 15:10:44 GMT

Try this

RangeSum(Above(Sum({<Year>}Value),0,6)) * Avg({<Year ={"2017"}>} 1)

Re: Filter on above function

Thu, 03 Aug 2017 15:26:14 GMT

Thank you guys, both of your solutions are working on my real case.

I've spent 1 day to find a solution, you are the best.

Thanks again.

Re: Filter on above function

Thu, 03 Aug 2017 15:26:58 GMT

Its working, thank you very much.

Re: Filter on above function

Thu, 03 Aug 2017 15:27:06 GMT

Its working, thank you very much.