topic Re: Sales by previous sales regardless the current selections in Search the Community

Sales by previous sales regardless the current selections

Leandro_Silva — Fri, 22 Jan 2021 20:59:40 GMT

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?

Re: Sales by previous sales regardless the current selections

skamath1 — Sat, 23 Jan 2021 16:34:33 GMT

You can use the below function available in Qlik

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

Re: Sales by previous sales regardless the current selections

Leandro_Silva — Tue, 26 Jan 2021 13:26:47 GMT

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?

Re: Sales by previous sales regardless the current selections

skamath1 — Tue, 26 Jan 2021 13:57:51 GMT

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?

Re: Sales by previous sales regardless the current selections

Leandro_Silva — Tue, 26 Jan 2021 14:19:02 GMT

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.

Re: Sales by previous sales regardless the current selections

Leandro_Silva — Tue, 26 Jan 2021 14:28:28 GMT

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.

Re: Sales by previous sales regardless the current selections

PradeepK — Tue, 26 Jan 2021 17:59:16 GMT

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

Re: Sales by previous sales regardless the current selections

Leandro_Silva — Tue, 26 Jan 2021 17:39:36 GMT

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.

Re: Sales by previous sales regardless the current selections

Leandro_Silva — Wed, 27 Jan 2021 12:57:02 GMT

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

Regards,

Leandro