How to mix in a chart a column chart, a line chart and the accumulation function?
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.
The same question
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:
When creating the union sub-query, I linked my time, invoice amount, and goal fields as seen below:
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
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:
When creating the union sub-query, I linked my time, invoice amount, and goal fields as seen below:
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
Hi Nathan,
Thank you so much for the detailed solution. It looks great! We'll do it in the same way!
Best Regards,
Joan.
Hi Nathan,
Thank you so much for the detailed solution. It looks great! We'll do it in the same way!
Best Regards,
Joan.
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
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
Hi Nathan,
Yes, we did. Everything worked fine, you can close the ticket.
Thank you!
Best Regards,
Joan.
Hi Nathan,
Yes, we did. Everything worked fine, you can close the ticket.
Thank you!
Best Regards,
Joan.
Replies have been locked on this page!