Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have 3 years data like 2022, 2021, and 2020.
for example:
Code year Sales
1 2022 250
2 2022 50
3 2022 200
4 2022 100
5 2022 500
6 2022 350
1 2021 340
2 2021 490
3 2021 250
4 2021 350
5 2021 200
6 2021 100
1 2020 250
2 2020 350
3 2020 200
4 2020 300
5 2020 500
6 2020 340
I want to calculate percentage of sales for each year like below
2022 2021 2020
code Sales %Sales Sales %Sales Sales %Sales
1
2
I am trying to use if( Year= '2022' , (Sum({<Year={2022}>}Sales)/Sum({<Year={2021}>}Sales))-1,
if( Year= '2021' , (Sum({<Year={2021}>}Sales)/Sum({<Year={2020}>}Sales))-1)
It is not working properly.
Can you please suggest me how to calculate % sales for each year.
Thanks & Regards,
Lakshman
Perhaps something along the lines of
Sum(Sales) / Sum(Total <Year> Sales)
for the percentage from this year's sale. If you're looking for the increase/decrease from the previous year, depending on the order, and you can use a pivot for the year rather than a hard-coded measure, either:
Sum(Sales) / Before(Sum(Sales))
or
Sum(Sales) / After(Sum(Sales))
Finally, in a straight table scenario, you could probably just reference the columns:
Column(1) / Column(3)
Hi!
In the script left join table to itself but add one to year and change field Sales to Sales_prev. If you will use a pivot table, then place the "Year" dimension horizontally and make two expressions. One sum(Sales) and other sum(Sales)/sum(Sales_prev).
Script logic:
Data:
load Code,
year,
Sales
from sales.qvd (qvd);
left join (Data)
load year+1 as year,
Code,
Sales as Sales_prev
Resident Data;