Qlik Sense App Development

Valued Contributor

how to show sum of rows on the basis of total rows..

Hi,

PFA.

Regards,

Kavita

1 Solution

Accepted Solutions
MVP

Re: how to show sum of rows on the basis of total rows..

Here is what you needed:

Quantity expression:

=Count({<

[Material - Material Level 01 (Key)] =

{"=Aggr(RangeSum(Above(If(Sum(Gross_Revenue)/10000000 > 0, Sum(Gross_Revenue)/10000000), 0, RowNo())), ([Material - Material Level 01 (Key)], (=If(Sum(Gross_Revenue)/10000000 > 0, Sum(Gross_Revenue)/10000000)))) <= 0.20*Sum(TOTAL Gross_Revenue)/10000000

and Sum(Gross_Revenue) > 0"}

>}[Net Billing Qty (Domestic)])

Gross_Revenue expression:

=Num(Sum({<

[Material - Material Level 01 (Key)] =

{"=Aggr(RangeSum(Above(If(Sum(Gross_Revenue)/10000000 > 0, Sum(Gross_Revenue)/10000000), 0, RowNo())), ([Material - Material Level 01 (Key)], (=If(Sum(Gross_Revenue)/10000000 > 0, Sum(Gross_Revenue)/10000000)))) <= 0.20*Sum(TOTAL Gross_Revenue)/10000000

and Sum(Gross_Revenue) > 0"}

>}Gross_Revenue)/10000000, '#,##0.0')

Do remember to test this with different selections to make sure that you are getting the desired result all the time. If you are not, then you might just need to add some set analysis. I am more than happy to help you out with that.

Also, you should be able to use this in the most recent version of Qlik Sense, not sure if this is possible in older version of Qlik Sense or not.

HTH

Best,

Sunny

40 Replies
Valued Contributor

Re: how to show sum of rows on the basis of total rows..

There are no materials with a gross revenue larger than 20% of the total.

talk is cheap, supply exceeds demand
Valued Contributor

Re: how to show sum of rows on the basis of total rows..

I want less than 20% of the gross revenue..not the largest..

My requirement is :

For e.g

Revenue    Quantity

M1          100          2

M2          200          1

M3          300          2

M4          400          1

M5          500          2

Total Revenue is 1500

20% is  300

So i want those materials quantity whose sum is less than or equal to 300 (cumulative).

i.e  M1, M2 quantity is 3.

Re: how to show sum of rows on the basis of total rows..

The text box in the qvw you posted says you do:

 I want those material's quantity whose sum >= 108. 20% of Gross Revenue).

But if there are no materials with a gross revenue larger that 20% of the total that means that all materials have less then 20% of the total gross revenue.

talk is cheap, supply exceeds demand
Valued Contributor

Re: how to show sum of rows on the basis of total rows..

I have given example in the comment box itself..

I mistakenly wrote greater than...it should be less than..

Also it should be cumulative less than  or equal to 20% not individually.

Please refer the example what i have mentioned in the comment box.

MVP

Re: how to show sum of rows on the basis of total rows..

May be this?

=Sum({<[Material - Material Level 01 (Text)] = {"=Sum(TOTAL Gross_Revenue)*0.20 < Sum(Gross_Revenue)"}>}[Net Billing Qty (Domestic)])

MVP

Re: how to show sum of rows on the basis of total rows..

Or may be this:

=Count({<[Material - Material Level 01 (Text)] = {"=Sum(TOTAL Gross_Revenue)*0.20 >= Sum(Gross_Revenue)"}>}[Net Billing Qty (Domestic)])

MVP

Re: how to show sum of rows on the basis of total rows..

Or may be this:

=Count({<[Material - Material Level 01 (Text)] = {"=Sum(TOTAL Gross_Revenue)*0.20 >= Sum(Gross_Revenue) and Sum(Gross_Revenue) > 0"}>}[Net Billing Qty (Domestic)])

Valued Contributor

Re: how to show sum of rows on the basis of total rows..

Its showing weird result.

For 2016 Aug

Gross Revenue is 66.8

and its 20% is 13.6

and we want those material's quantity whose cumulative total is 13.6 or less than 13.6.

So, the count of quantity should be 118.