topic Re: RangeSum() and RowNo() in QlikView

RangeSum() and RowNo()

nictheswede — Wed, 25 Nov 2020 16:16:04 GMT

I use the following expression to add the first % to the next and so on:

rangesum(Above(([Sales]/AGGR(NODISTINCT SUM([Sales]),PG)),0, RowNo()))

When i choose another dimension (KC) in addition to PG I would like to keep the same percentages as i had when only PG was chosen but sort out KC.

I tried to add a SET to the expression but the it will only count the visable results.

rangesum(Above(([Sales]/AGGR(NODISTINCT SUM({<KC=>}[Sales]),PG)),0, RowNo()))

I hope my explanation is somewhat understandable.

Re: RangeSum() and RowNo()

sunny_talwar — Fri, 02 Feb 2018 16:56:04 GMT

Try this

RangeSum(Above(([Sales]/Only({<KC>}AGGR(NODISTINCT SUM({<KC=>}[Sales]),PG))), 0, RowNo()))

Re: RangeSum() and RowNo()

nictheswede — Mon, 05 Feb 2018 08:18:36 GMT

I tried your suggestion but it didn´t give the answer I was looking for.

The diagram shows all the rows but it doesn´t count the values.

This example, I think, explains how I mean:

Original
PG	ID	KC	%	Ack %
HEMACCENT	1	IL	40,0%	40,0%
HEMACCENT	2	PB	25,0%	65,0%
HEMACCENT	3	PB	20,0%	85,0%
HEMACCENT	4	IL	10,0%	95,0%
HEMACCENT	5	IL	5,0%	100,0%

I would like the expression to do this:

KC=PB
PG	ID	KC	%	Ack %
HEMACCENT	2	PB	25,0%	65,0%
HEMACCENT	3	PB	20,0%	85,0%
KC=IL
PG		KC	%	Ack %
HEMACCENT	1	IL	40,0%	40,0%
HEMACCENT	4	IL	10,0%	95,0%
HEMACCENT	5	IL	5,0%	100,0%

But I get:

KC=PB
PG	ID	KC	%	Ack %
HEMACCENT	2	PB	25,0%	25,0%
HEMACCENT	3	PB	20,0%	45,0%
KC=IL
PG	ID	KC	%	Ack %
HEMACCENT	1	IL	40,0%	40,0%
HEMACCENT	4	IL	10,0%	50,0%
HEMACCENT	5	IL	5,0%	55,0%

Re: RangeSum() and RowNo()

sunny_talwar — Mon, 05 Feb 2018 12:26:26 GMT

May be you need this

RangeSum(Above(Only({<KC>}[Sales]), 0, RowNo()))/Sum(TOTAL <PG> [Sales])

Re: RangeSum() and RowNo()

qliksus — Mon, 05 Feb 2018 12:28:44 GMT

Maybe something like this

sum(aggr(rangesum( Above(sum({1}per),0,Rowno(total))),PG,ID))

Re: RangeSum() and RowNo()

nictheswede — Mon, 26 Feb 2018 14:56:38 GMT

Thanks for your input but it won´t work for me.

I don´t get the values that I sorted out to add when they are not visible.

Re: RangeSum() and RowNo()

nictheswede — Mon, 26 Feb 2018 14:56:46 GMT

Thanks for your input but it won´t work for me.

I don´t get the values that I sorted out to add when they are not visible.

Re: RangeSum() and RowNo()

sunny_talwar — Mon, 26 Feb 2018 14:58:52 GMT

Okay ... not sure how to help without looking at this... more than happy to help if you are able to share a sample

Re: RangeSum() and RowNo()

nictheswede — Tue, 27 Feb 2018 10:26:18 GMT

My goal is to make work just like a filter in Excel.

I would like to keep the Ack% for each ID but when i choose KC I get a new value based on the new conditions.

Original table:
PG	ID	KC	%	Ack %
HEMACCENT	1	IL	40,0%	40,0%
HEMACCENT	2	PB	25,0%	65,0%
HEMACCENT	3	PB	20,0%	85,0%
HEMACCENT	4	IL	10,0%	95,0%
HEMACCENT	5	IL	5,0%	100,0%

I would like the expression to do this:

If I choose KC=PB
PG	ID	KC	%	Ack %
HEMACCENT	2	PB	25,0%	65,0%
HEMACCENT	3	PB	20,0%	85,0%
If I choose KC=IL
PG		KC	%	Ack %
HEMACCENT	1	IL	40,0%	40,0%
HEMACCENT	4	IL	10,0%	95,0%
HEMACCENT	5	IL	5,0%	100,0%

But I get:

KC=PB
PG	ID	KC	%	Ack %
HEMACCENT	2	PB	25,0%	25,0%
HEMACCENT	3	PB	20,0%	45,0%
KC=IL
PG	ID	KC	%	Ack %
HEMACCENT	1	IL	40,0%	40,0%
HEMACCENT	4	IL	10,0%	50,0%
HEMACCENT	5	IL	5,0%	55,0%

Re: RangeSum() and RowNo()

sunny_talwar — Tue, 27 Feb 2018 12:06:30 GMT

I give up...

Someone else might be able to help you better.

Best,

Sunny

Re: RangeSum() and RowNo()

pablolabbe — Tue, 27 Feb 2018 19:28:23 GMT

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others