how to find percentages in straight table

[Lakshmanudui]

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

[Or]

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)

[Onerealy]

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;