Solved: how to show sum of rows on the basis of total rows... - Qlik Community

kavita25 · ‎2016-12-06

Hi,

PFA.

Regards,

Kavita

sunny_talwar · ‎2016-12-14

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

View solution in original post

kavita25 · ‎2016-12-06

stalwar1‌

Please help me with this issuee...

Gysbert_Wassenaar · ‎2016-12-06

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

talk is cheap, supply exceeds demand

kavita25 · ‎2016-12-06

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.

Gysbert_Wassenaar · ‎2016-12-06

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

kavita25 · ‎2016-12-06

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.

sunny_talwar · ‎2016-12-06

May be this?

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

sunny_talwar · ‎2016-12-06

Or may be this:

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

sunny_talwar · ‎2016-12-06

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)])

kavita25 · ‎2016-12-06

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.