Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I made a straight table and need to know the total sum of the sales of the past 3 years per ordernumber and hope you are able to help me.
Example:
Ordernummer = 1
Sales per year:
2018 = 80
2017 = 50
2016 = 70
Total of last 3 years for this order = 200
I made a variable for the last 3 years: vLast3Year = Year(YearStart(Today(),-2))
And the expression:
Sum({$<Year={'>=$(vLast3Year)'}>} total(sales) )
When I select the ordernumber the total sum amount of the last 3 years is correct and visible per year:
Year | Order number | Sales | Total sum Last 3 Year |
2018 | 1 | 80 | 200 |
2017 | 1 | 50 | 200 |
2016 | 1 | 70 | 200 |
2015 | 1 | 60 | 200 |
2014 | 1 | 90 | 200 |
However, when I select all orders per year, then the total is the sum of all ordernumbers of the past 3 years, instead per ordernumber (it should be the data in green):
Year | Order number | Sales | Total sum Last 3 Year | Total sum Last 3 Year |
2018 | 1 | 80 | 2450 | 200 |
2018 | 2 | 200 | 2450 | 700 |
2018 | 3 | 20 | 2450 | 100 |
2018 | 4 | 500 | 2450 | 1200 |
2018 | 5 | 100 | 2450 | 250 |
Thanks in advance.
Regards,
Marielle
Het bericht is bewerkt door: Marielle Kandt
Het bericht is bewerkt door: Marielle Kandt
Try this
Sum({$<Year = {'>=$(vLast3Year)'}>} TOTAL <[Order number]> sales)
Try this
Sum({$<Year = {'>=$(vLast3Year)'}>} TOTAL <[Order number]> sales)
Yes it is working! Thanks for your support.