If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Running-Total and Sub-Total

Started by Trippple, 02 Apr 2020 05:56:24 AM

Previous topic - Next topic

Trippple

Hello from Germany, I hope you are all healthy and not too badly affected by Corona.

I've working on a problem on sub-totals and running total for many hour and could not find a solution. There's a crosstable with Week and Date, and two measures "Sales" and "Margin". I want to have a subtotal after each weeks - that works finde. And I also want to have an running total in the column right next to "Sales". Same thing to "Margin". The running total works until my first subtotal, then the subtotal adds up the accumulated values again. I've tried many ways, read many posts but could not figure it out.

Below my crosstab, cause of the different ways of counting weeks and format dates in the world, I've displayed it in an easy way for better understanding. Maybe somebody could help, it would be very very fine, thanks in advance!


Week   Date      Sales   Margin   Running-Total Sales      Running-Total Margin   
1      Day 01        10      5              10                             5   
1      Day 02        10      5              20                            10   
1      Day 03        10      5              30                            15   
1      Day 04        10      5              40                            20   
1      Day 05        10      5              50                            25   
1      Day 06        10      5              60                            30   
1      Day 07        10      5              70                            35   
      ƩWeek 1       70      35         140   Correct: 70        70   Correct: 35
2      Day 08        15      10            155                            80   
2      Day 09        15      10            170                            90   
2      Day 10        15      10            185                           100   
2      Day 11        15      10            200                           110   
2      Day 12        15      10            215                           120   
2      Day 13        15      10            230                           130   
2      Day 14        15      10            245                           140   
      ƩWeek 2       105      70         350   Correct: 175   210   Correct: 105



seb24c

Can you try changing your subtotal to aggregate? In the properties, change Summary from Total to Aggregate. I think it should then total the first columns and not total the running-totals.