# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
New Contributor II

## RangeSum() and RowNo()

Hi

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.

Tags (2)
10 Replies
MVP

## Re: RangeSum() and RowNo()

Try this

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

New Contributor II

## Re: RangeSum() and RowNo()

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%
MVP

## Re: RangeSum() and RowNo()

May be you need this

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

Valued Contributor II

## Re: RangeSum() and RowNo()

Maybe something like this

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

New Contributor II

## Re: RangeSum() and RowNo()

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.

New Contributor II

## Re: RangeSum() and RowNo()

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.

MVP

## Re: RangeSum() and RowNo()

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

New Contributor II

## Re: RangeSum() and RowNo()

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%
MVP

I give up...