Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
nictheswede
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

Re: RangeSum() and RowNo()

Try this

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

nictheswede
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
PGIDKC%Ack %
HEMACCENT1IL40,0%40,0%
HEMACCENT2PB25,0%65,0%
HEMACCENT3PB20,0%85,0%
HEMACCENT4IL10,0%95,0%
HEMACCENT5IL5,0%100,0%

I would like the expression to do this:

 

KC=PB
PG IDKC%Ack %
HEMACCENT2PB25,0%65,0%
HEMACCENT3PB20,0%85,0%

KC=IL

PG KC%Ack %
HEMACCENT1IL40,0%40,0%
HEMACCENT4IL10,0%95,0%
HEMACCENT5IL5,0%100,0%

But I get:

KC=PB
PG IDKC%Ack %
HEMACCENT2PB25,0%25,0%
HEMACCENT3PB20,0%45,0%

KC=IL

PG IDKC%Ack %
HEMACCENT1IL40,0%40,0%
HEMACCENT4IL10,0%50,0%
HEMACCENT5IL5,0%55,0%

Re: RangeSum() and RowNo()

May be you need this

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

qliksus
Valued Contributor II

Re: RangeSum() and RowNo()

Maybe something like this

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

nictheswede
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.

nictheswede
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.

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

nictheswede
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:
PGIDKC%Ack %
HEMACCENT1IL40,0%40,0%
HEMACCENT2PB25,0%65,0%
HEMACCENT3PB20,0%85,0%
HEMACCENT4IL10,0%95,0%
HEMACCENT5IL5,0%100,0%

I would like the expression to do this:

If I choose KC=PB
PGIDKC%Ack %
HEMACCENT2PB25,0%65,0%
HEMACCENT3PB20,0%85,0%

If I choose KC=IL

PGKC%Ack %
HEMACCENT1IL40,0%40,0%
HEMACCENT4IL10,0%95,0%
HEMACCENT5IL5,0%100,0%

But I get:

KC=PB
PGIDKC%Ack %
HEMACCENT2PB25,0%25,0%
HEMACCENT3PB20,0%45,0%

KC=IL

PGIDKC%Ack %
HEMACCENT1IL40,0%40,0%
HEMACCENT4IL10,0%50,0%
HEMACCENT5IL5,0%55,0%

Re: RangeSum() and RowNo()

I give up...

Someone else might be able to help you better.

Best,

Sunny