40 Replies Latest reply: Dec 16, 2016 8:36 AM by Sunny Talwar

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

Hi,

PFA.

Regards,

Kavita

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

stalwar1

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

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

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

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

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

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

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

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

What exactly are you counting here? This?

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

The second column..

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

So the first 11 rows meet your definition of Total Gross Revenue's 20% is less than or equal to gross revenue. How are you counting 118 here? I mean the 11th row itself is 120

What am I missing her?

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

20% is 13.6...

And we want those quantity whose gross revenue is 13.6 or less than 13.6..

So..

GRevenue                   Quantity

 MAHINDRA 265 DI S PS 0.2 4 MAHINDRA 415 DI PS CRPTO TRACTOR 1.1 15 MAHINDRA 265 DI S MECH 1.4 20 MAHINDRA YUVO 275 DI PS TRACTOR 2.6 39 MAHINDRA YUVO 415 DI PS TRACTOR 3.7 40

Total                       9                  118

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

You made this whole lot more complicated now

There is not straight forward way to do this, but see if this links help:

ABC Analysis in Qlikview

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

But i hope you understand my requirement  better now..

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

I absolutely do.... but this require me to accumulate in a text box object which is not very straight forward as I would like

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

Can't be done really. Unless you precalculate stuff in the script and have QV 12 so you can use the sort feature of the aggr function. Or if you use actions that involve Pareto Select and messing about with alternate states.

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

Yup, I agree.... John and I came up with this two button strategy

ABC Analysis in Qlikview

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

Well I should not take credit for John's work here... John came up with this two button strategy

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

Okay..il try..

Actually I want to use this in qlik sense..

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

Hahahahaha you made this difficult to nearly impossible now. Nearly impossible because I am not sure what options are available in Qlik Sense for pareto select in Qlik Sense or may be there might be an easier extension which you might be able to use. I am not sure how this can be done in Qlik Sense

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

You shouldn't give me credit for your work either! You wrote the first button, and all the second button does is fix the problem with the first button, so I'd say you can take more credit than I can for the solution. The two buttons can be turned into a single button using a macro, which allows us to explicitly sequence what's going on, and that's definitely what I'd do at a minimum if this were my application.

I am, unfortunately, still completely unfamiliar with Qlik Sense, so I don't know if that's possible, and won't be able to help, I'm afraid.

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

Can we do something in back end..so that cumulative is captured...??

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

You might be able to do the aggregation in the back end, but then your selections won't change your results. Is that something acceptable?

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

I just want the selections to be changed on date.. and show cumulative based on the Month and Year..

Is that can be done??

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

That might be possible. Would you be able to share your qvd?

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

Hi,

PFA.

Please if possible help me with this issue..

I am really stucked.

Regards,

Kavita

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

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

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

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

Is that possible?

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

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.

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

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

Is it?

stalwar1gwassenaarjohnw

RangSum in Text Object

In the above link..its successfully done.

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

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

• 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

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

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

Il definetely try this out...

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

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

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

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

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

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

U always helped me...

Really appreciate ur help...

Also explain me the logic..

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

I was only able to help because of the new functionality that Qlik have introduced. Without this functionality, I would have not been able to help at all. Please mark correct and any helpful responses to close this thread down.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny