Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Lakshmanudui
Contributor II
Contributor II

how to find percentages in straight table

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

 

 

 

Labels (1)
2 Replies
Or
MVP
MVP

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
Contributor
Contributor

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;

Onerealy_0-1660841182753.png