topic Re: Cumulative Count with null values in App Development

Cumulative Count with null values

Amal — Fri, 21 Jan 2022 16:52:44 GMT

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 !

Re: Cumulative Count with null values

Ksrinivasan — Sat, 22 Jan 2022 01:08:27 GMT

Hi,

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

Re: Cumulative Count with null values

anat — Sat, 22 Jan 2022 07:37:02 GMT

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;

Re: Cumulative Count with null values

anat — Sat, 22 Jan 2022 07:41:38 GMT

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
];

Re: Cumulative Count with null values

Amal — Mon, 24 Jan 2022 09:07:43 GMT

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.

Re: Cumulative Count with null values

Amal — Mon, 24 Jan 2022 10:09:48 GMT

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