Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
PFA.
Regards,
Kavita
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
Please help me with this issuee...
There are no materials with a gross revenue larger than 20% of the total.
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.
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.
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.
May be this?
=Sum({<[Material - Material Level 01 (Text)] = {"=Sum(TOTAL Gross_Revenue)*0.20 < Sum(Gross_Revenue)"}>}[Net Billing Qty (Domestic)])
Or may be this:
=Count({<[Material - Material Level 01 (Text)] = {"=Sum(TOTAL Gross_Revenue)*0.20 >= Sum(Gross_Revenue)"}>}[Net Billing Qty (Domestic)])
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)])
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.