How to calculate running sum and create buckets

[farheenayesha]

Hi,

     I want to calculate running sum for accounts and also create buckets using running sum % values as below. Please help me to correct formulas

1. Running sum of sales: i have used below expression to calculate it.

          RangeSum(Above(sum(SLS_AMT),0,RowNo()))

2. Running sum % :

           RangeSum(Above(sum(SLS_AMT),0,RowNo()))/sum(TOTAL SLS_AMT)

3. Segments :

          if(Running sum % <=0.2, 'Top 20%',

                 if(Running sum % >0.2 and Running sum % <=0.4 , 'Middle 20%','Bottom 40%'))

Using the above buckets i want to create a bar chart as below. Please help me to create the below bar chart.

[sunny_talwar]

If you have QV12.1 or above, you can try the attached file

[farheenayesha]

Hi Sunny,

     Thanks for your quick response. The values are not matching exactly with my excel version. It would be great, if you could help me to get the below values.

[sunny_talwar]

I don't see this chart in your attached Excel file...

[farheenayesha]

Hi Sunny,

     Please find the excel file with chart attached in the original post.

[sunny_talwar]

Is Segment available in the database? How is this created if it isn't available? How do you know that Account 1 is Bottom 40%?

[farheenayesha]

Segment is not available. Actually Segment is created as below.

1. Sort the Sls_Amt in ascending order for Accounts.

2. Calculate running sum% of SLS_AMT as mentioned above.

3. Create Segments as

if(Running sum % <=0.2, 'Top 20%',

                 if(Running sum % >0.2 and Running sum % <=0.4 , 'Middle 20%','Bottom 40%'))

Please let me know if you need more clarification on Segments.

[sunny_talwar]

Can you show where you do this? In Excel? or is this outside of the Excel?

[farheenayesha]

Hi Sunny,

     Please find the excel with calculations of segments.

[sunny_talwar]

Yet again I don't understand how do you created 2016 Sales tab in your Excel sheet? I don't even see ACCT_NO 56320, but it appeared on 2016 Sales tab.... how are these Sales numbers generated