Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Amal
Partner
Partner

Cumulative Count with null values

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 !

5 Replies
Ksrinivasan
Specialist
Specialist

Hi,

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

anat
Specialist II
Specialist II

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
Specialist II
Specialist II

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
Partner
Partner
Author

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
Partner
Partner
Author

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