topic Re: Above with null value in QlikView

Above with null value

19iv1987 — Wed, 25 Nov 2020 16:16:04 GMT

Hi to everyone, I created in Qlik Front-end a straight table like the one attached.

My aim is to obtain the result in column E and so the cumulative sum of column C.

But for now I only managed to obtain the result in column D thanks to this expression:

sum(AGGR(RangeSum(above( COUNT({SetAnalysis} %KEY_INT),0,RowNo())),N))

I have problems to deal with null values...

Thanks in advance

Eva

Re: Above with null value

sunny_talwar — Mon, 11 Sep 2017 13:05:41 GMT

Would you be able to share a qvw sample to take a look at this?

Re: Above with null value

Anil_Babu_Samineni — Mon, 11 Sep 2017 13:07:20 GMT

Can this work to you? i am thinking

sum(AGGR(RangeSum(above( COUNT({<%KEY_INT = {'=Len(%KEY_INT) > 0'}>} %KEY_INT),0,RowNo())),N))

My Bad, It should Double Quote

sum(AGGR(RangeSum(above( COUNT({<%KEY_INT = {"=Len(%KEY_INT) > 0"}>} %KEY_INT),0,RowNo())),N))

Re: Above with null value

19iv1987 — Mon, 11 Sep 2017 13:20:00 GMT

Hi Sunny, for me it's not possible to share qvw just beacause it's a reserved huge project. I tried to make a smaller example and in that case all seems to work fine.. I am starting to thik that there's a problem in the star schema...

Re: Above with null value

19iv1987 — Mon, 11 Sep 2017 13:21:45 GMT

not working for me

Re: Above with null value

Anil_Babu_Samineni — Mon, 11 Sep 2017 13:24:34 GMT

Atleast can you show the image? Where you are getting null values?

Re: Above with null value

19iv1987 — Mon, 11 Sep 2017 13:34:14 GMT

For me the problem is that I forced to display all N in dataset in the straight table even though N° Claims is not

valorized for the specific subset of data taking into account.

Re: Above with null value

Anil_Babu_Samineni — Mon, 11 Sep 2017 13:36:42 GMT

May be enabling the option "Suppress when value is null"? Because, Without seeing application it may difficult to think.

Re: Above with null value

19iv1987 — Mon, 11 Sep 2017 13:44:51 GMT

No beacause I have to display all N also if value is null.

if N° Claims is null I want to display the same comulative sum of the previous not null row.

Re: Above with null value

Anil_Babu_Samineni — Mon, 11 Sep 2017 14:01:06 GMT

May be this?

Sum(Aggr(If(IsNull([N° Claims]), [N° Claims], sum(AGGR(RangeSum(above( COUNT(%KEY_INT),0,RowNo())),N))), [Delta gg], N))

Re: Above with null value

effinty2112 — Mon, 11 Sep 2017 14:55:26 GMT

Hi Eva,

Maybe this could work for you, I'm not showing the full straight table:

dd	[No claims]	Rangesum	Desired
Fino a 8,00	6744	6744	6744
Fino a 38,42	8099	14843	14843
Fino a 68,83	1567	16410	16410
Fino a 99,25	383	16793	16793
Fino a 129,67	286	17079	17079
Fino a 160,08	250	17329	17329
Fino a 190,50	144	17473	17473
Fino a 220,92	56	17529	17529
Fino a 251,33	30	17559	17559
Fino a 281,75	11	17570	17570
Fino a 312,17	27	17597	17597
Fino a 342,58	23	17620	17620
Fino a 373,00	3	17623	17623
Fino a 403,42	1	17624	17624
Fino a 433,83	0	17624	17624
Fino a 464,25	0	17624	17624
Fino a 494,67	1	17625	17625
Fino a 525,08	2	17627	17627
Fino a 555,50	1	17628	17628
Fino a 585,92	0	17628	17628
Fino a 616,33	2	17630	17630
Fino a 646,75	0	17630	17630
Fino a 677,17	2	17632	17632
Fino a 707,58	0	17632	17632
Fino a 738,00	0	17632	17632
Fino a 768,42	1	17633	17633
Fino a 798,83	0	17633	17633
Fino a 829,25	0	17633	17633
Fino a 859,67	0	17633	17633
Fino a 890,08	0	17633	17633

There is a single dimension : dd

[No claims] is entered as an expression and the expression titled Rangesum is:

Rangesum(above([No claims],0,RowNo()))

Good luck

Andrew

Re: Above with null value

19iv1987 — Mon, 11 Sep 2017 15:17:50 GMT

Hi Andrew, look at the last column in below image ...it doesn't work

Re: Above with null value

effinty2112 — Mon, 11 Sep 2017 15:33:31 GMT

Hi Eva,

How many dimensions do you have in your table? The expression will not work if there is more than one. Replace the dimensions except one with expressions.

all the best

Andrew