Solved: Re: Sales by previous sales regardless the current... - Qlik Community

Leandro_Silva · ‎2021-01-22

Hello!

I have a table that could be summarized like the one below:

date	sales
10/1/2020	5
9/1/2020	4
8/1/2020	2
7/1/2020	4

I want to calculate the growth percentage of sales between the current month and the previous month, regardless the selections. To illustrate that the calculations are show below (under the expression current sales/previous sales -1)

date	sales	previous sales	growth
10/1/2020	5	4	0.25
9/1/2020	4	2	1
8/1/2020	2	4	-0.5
7/1/2020	4	0	0

How can i do that? I've tried to use set analysis and inter-record functions but nothing worked. Someone can help me?

PradeepK · ‎2021-01-26

Assuming your fact data is at Monthly granularity.. you can easily calculate growth for each month in load Script.

Use below code for reference.

Example:
Load * Inline [
date	sales	 
10/1/2020	5	
9/1/2020	4	
8/1/2020	2	
7/1/2020	4	
](delimiter is '\t');


Temp:
Load
	*,
    ( Sales / PreviousSales ) - 1  as growth;
Load
	date as Date,
	sales as Sales,
    Alt(Previous(sales),0) as PreviousSales
Resident Example
ORDER BY date ASC;

drop Table Example;

Use Set Identifier '1' to avoid any selection changes..

View solution in original post

skamath1 · ‎2021-01-23

You can use the below function available in Qlik

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

Leandro_Silva · ‎2021-01-26

Hi! I've tried to use the below() function but it's not working and i don't know why. I am using the formula: below(sales), where sales is the header of the column that i want to take the value below. This formula is returning nothing.

Also, the way to solving my problem is to use these inter-record functions?

skamath1 · ‎2021-01-26

If you look at the help and see the examples, it mentioned that you have to use the aggregation function below. Use the expression below(sum(sales)) if the name of the column or field name is sales.

Do you want to compute in the script or in the table chart object?

Leandro_Silva · ‎2021-01-26

I want to compute that in the script. It's mandatory for me to make any changes in the data in another plataform.

Sales is a field and still the formula below(sum(sales)) it isn't returning anything. I also tried to use below(column(<number_of_the_column>)) as well, but without any sucess.

Leandro_Silva · ‎2021-01-26

Do you if it's possible to use some sort of set analysis on this? I've tried the formula below:

Sum({$<date={"$(=Date(Addmonths(Max(date),-1),'YYYY-MM-DD'))"}>}sales)

to take the previous values, but it only works when i select a date value. And i would like to showcase all values regardless any selections.

PradeepK · ‎2021-01-26

Assuming your fact data is at Monthly granularity.. you can easily calculate growth for each month in load Script.

Use below code for reference.

Example:
Load * Inline [
date	sales	 
10/1/2020	5	
9/1/2020	4	
8/1/2020	2	
7/1/2020	4	
](delimiter is '\t');


Temp:
Load
	*,
    ( Sales / PreviousSales ) - 1  as growth;
Load
	date as Date,
	sales as Sales,
    Alt(Previous(sales),0) as PreviousSales
Resident Example
ORDER BY date ASC;

drop Table Example;

Use Set Identifier '1' to avoid any selection changes..

Leandro_Silva · ‎2021-01-26

I the PreviousSales and the growth into the script but it's only given me the value zero. But it should return a table like you showed above (the Temp table). I don't know why Previous() or Below() functions are not working.

Leandro_Silva · ‎2021-01-27

Hi PradeepK! Thanks for the help. I fixed my code and now i am using your solution.

Regards,

Leandro

Sales by previous sales regardless the current selections