Cumulative Count with null values

[Amal]

Hello everyone, 

I want a line chart with a cumulative count in my Qlik Sense App. So, what I have done is this expression : 

Aggr(
RangeSum(
Above(COUNT ( VEHICULE_NUMBER ), 0, RowNo())
)
,[Date]
)

In the dimension, I have a date. 

Although, for some date, I don't have VEHICULE_NUMBER so, the line chart is not continous, it has blanks.

What I want here, is that instead of the null value, to pick up the last value of my count which was not null. For exemple in my line chart I have : 

date	what I have	what I want
2014	14	14
2015	18	18
2016	-	18
2017	-	18
2018	20	20
2019	21	21

I tried to simplify my count here for you to understand the problem, but the set analysis is huge and contains many conditions. 

Do you have any suggestions ? 

I tried to take the value "above" but in my example, it fulled only the line 2016 and not the 2017. I need it to take the last not null count. 

Thanks !

[Ksrinivasan]

Hi,

Aggr(RangeSum(above(sum(Sales),0,3)),Product,DATE)

[anat]

t1:
load * inline [
dt,val
2014,14
2015,18
2016,
2017,
2018,20
2019,21
];

noconcatenate
t:
load *,if(len(val)=0,peek(t),val) as t resident t1 order by dt;
drop table t1;
exit script;

[anat]

t1:
load *,if(len(val)=0,peek(t),val) as t;
load * inline [
dt,val
2014,14
2015,18
2016,
2017,
2018,20
2019,21
];

[Amal]

Hello, thanks for your replies. 

@anat peek function is only usable in the script. I would like a solution to use in the graph. My count function contains a lot of Set Analytics. Do you have any other solutions ? Thanks, I will see if something like this is possible.

@Ksrinivasan I want that the function takes the last non-null value, putting '3' will not adapte to every situations, thanks anyway.

[Amal]

hello @Kamryn , still looking for a solution. I will share it if I find something. Don't hesitate to share as well 🙂