Adding subtotals to a reports showing the difference between this year and the last year

2 posts / 0 new
Last post
tp5harp
Adding subtotals to a reports showing the difference between this year and the last year

Hi All,

I am trying to add a report as below.  I would like to create a table with Sub Totals, based on each product in the database, showing the difference between the sales from this year to the sales from last year, with the grand total of the differences at the end.

Product Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
Product 1 2014 59000 60456 72104 63368 64824 66280 67736 69192 70648 72104 73560 59000 798272
Product 1 2015 63000 59222 79241 64602 63870 68833 66408 69391 69414 72008 72326 57766 806081
Variance   4000 -1234 7137 1234 -954 2553 -1328 199 -1234 -96 -1234 -1234 7809
Product 2 2014 59000 60456 72104 63368 64824 66280 67736 69192 70648 72104 73560 59000 798272
Product 2 2015 63000 59222 79241 64602 63870 68833 66408 69391 69414 72008 72326 57766 806081
Variance   4000 -1234 7137 1234 -954 2553 -1328 199 -1234 -96 -1234 -1234 7809
Grand Total   8000 -2468 14274 2468 -1908 5106 -2656 398 -2468 -192 -2468 -2468 15618

 When I add groupings I do not get the details lines, just the sub totals, and I cannot see how to add a summary line to show the difference between the two values.  Are these both possible in BusinessAnalyze.

Many thanks,

Trevor

Anders
Anders's picture
Hi Trevor

Hi Trevor and happy new year!

We currently support only SUM on subtotals which means that we do not support "product 1 2015" – "product 1 2014" (DIFF).
We do not support pivoting with subtotals either since there can be max 2 text-columns during pivot (the below view would require three columns: month,year,product).

What we support is the below subtotals (where the total column is calculated in the database query):

Year

Product

Jan

Feb

Total

2014

 

 

 

 

 

Product 1

2

3

5

 

Product 2

3

4

7

2014

 

5

7

12

2015

 

 

 

 

 

Product 1

5

6

11

 

Product 2

7

8

15

2015

 

12

14

26

 

Product

Year

Jan

Feb

Total

Product 1

 

 

 

 

 

2014

2

3

5

 

2015

3

4

7

Product 1

 

5

7

12

Product 2

 

 

 

 

 

2014

5

6

11

 

2015

7

8

15

Product 2

 

12

14

26

An other option is to create two levels with drilldown:
- First level products with Year Variance
- Drilldown with year-values for selected product

 

Regards,

Anders

Log in or register to post comments