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

[kavita25]

Hi,

PFA.

Regards,

Kavita

[sunny_talwar]

You are accumulating based on Material here -> in other words Sum(Gross Revenue) by material, right? Selections in Month and Year are going to change these sums. I don't think this is going to work. I did not open your qvw before my previous response. If you can live without making selections in Year and Month, then it might work

[kavita25]

Material wise cumulative..but it should show Year and Month wise also..

Is that possible?

[sunny_talwar]

Then we are talking about creating multiple version of accumulation. Assuming you have 20 periods, you will have to create 20 different Material wise accumulation.

Kavita, there is no easy way to do this unfortunately.

[kavita25]

Okay..I have to create accumulation for All the months with the Group by of Material?

Is it?

[sunny_talwar]

Just what you needed to resolve this issue.

Recipe for a Pareto Analysis – Revisited‌

You can now sort within your Aggr() function using an expression which was not previously available. I have not tested it out yet, but plan to work on this tonight. You can try to play around with this as well.

Best,

Sunny

[sunny_talwar · Accepted solution]

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

[kavita25]

Yeah Thank U soo much..for ur efforts..

Il definetely try this out...

[kavita25]

Its showing 0 in the text box...Is it working in Qlikview 12?

[sunny_talwar]

I am using QV 12.1 and it worked for me. It should also work in latest version of Qlik Sense

[kavita25]

Thank You So Much...Its working fine...

U always helped me...

Really appreciate ur help...

Also explain me the logic..