How to mix in a chart a column chart, a line chart and the accumulation function?

Joan Poblet shared this question 7 years ago
Answered

Hi,


We are creating a complex invoicing report (union subquery) with the following information:


- Master query: real invoicing per month from the beginning of the year to the last month

- Union subquery: invoicing forecast from current month to the end of the year


The columns are:

- Month

- Invoicing amount (Sum)

- Target (a fixed value that we would like to use to compare with the accumulation)


When we go to the Charts step, we would like to have three series in the same chart with different layouts:

1. Column chart: X: Month / Y: Monthly invoice

2. Linear chart: X: Month / Y: Yearly target invoice (a horizontal line)

3. Accumulation function: X: Month / Y: Accumulated invoice (same Y axis as series 2).


Moreover, we would like to be able to colourize in a different way the months which are from the Master query and the months which are from the Slave query.


We tried with combined chart but the Accumulation functions wasn't available.


Is there any way to be able to achieve the previous requirements?


Many thanks for your help.


Best Regards,

Joan.

Replies (4)

photo
1

Hi Joan,

Thank you for reaching out. In my simple test, I was able to accomplish the goal of applying an accumulation line to an overlay chart on a union sub-query. Unfortunately, because union sub-queries combine fields, it is not possible to differentiate the two after the link has been made. However, 7.3 will include many additional aesthetic report building features, so individual coloring may be possible in the future.

To apply an accumulation line to a combination chart, I added an additional invoice column to my primary report, applying the "Accumulative total" advanced function to this second field, as seen below:

158cc310c02443e5fd0d0ac0f7ec433a

When creating the union sub-query, I linked my time, invoice amount, and goal fields as seen below:

df0277bfa1adca2bf4fd17aef2437473

The calculated field will apply itself to the additional data. In charts you can then specify the axes and chart types to your liking.

Please let me know if this does not work for you, or if you have any additional questions relating to this issue.

Regards,

Nathan

photo
1

Hi Nathan,


Thank you so much for the detailed solution. It looks great! We'll do it in the same way!


Best Regards,

Joan.

photo
1

Hi Joan,


I was just wondering if you have had any luck in resolving this issue? Please let me know if you have any additional questions, or if you would be okay with me closing this ticket.


Regards,


Nathan

photo
1

Hi Nathan,


Yes, we did. Everything worked fine, you can close the ticket.


Thank you!


Best Regards,

Joan.

Leave a Comment
 
Attach a file