0

Groupby

I need to display the cumulative sum of rows grouped by category.

In my clip I have 100 data which are all defects opened each day .I have created another column called weeks and displayed which week these defects are created . Now, I need to sum up these defects every week eventually displaying the total sum.

example if i have 2 defects last week and 3 defects this week

then the total sum for last week was 2 and this week is 5 .

Can someone help ?

Thanks 

 

1 comment

  • 0
    Avatar
    Janice Janczyn

    Hi Aswini,

    If your dates are in chronological order and you do not need to apply a sort to your table klip, you can do something like the following using the Actions menu. 

    1. Point the 1st column (Dates) to the dates in your datasource, ensuring the Input Format matches the format of the dates in your data and set the Display Format to yyyy-ww to display week numbers.
    2. Point the 2nd column (Defect Count) to the defect count in your datasource.
    3. On the 1st column, click the 3 dots menu and select Group. On the 2nd column, set Aggregation > Sum.
    4. In the 3rd column, use the CUMULATIVE function and reference the 2nd column: CUMULATIVE( &Defect Count ).

    I hope this helps. If you do need to sort your dates, you will have to do the equivalent using functions instead of actions to get the CUMULATIVE function to sum the data in the correct order.

    Thanks,
           Janice

Please sign in to leave a comment.