topic Re: Current vs Previous in QlikView

Current vs Previous

andrea90casa — Wed, 25 Nov 2020 16:16:04 GMT

Hi at all,

I have a data set like this one

Month_ID	Date	Month	Amount
1	01/01/2017	Gen	20
2	01/02/2017	Feb	37
3	01/03/2017	Mar	17
4	01/04/2017	Apr	50
5	01/05/2017	May	14
6	01/06/2017	Jun	22
7	01/07/2017	Jul	18
8	01/08/2017	Ago	10
9	01/09/2017	Sep	25
10	01/10/2017	Oct	24
11	01/11/2017	Nov	13
12	01/12/2017	Dec	19

I create a simple table with months on rows and sum(amount) on first column. Than i created a calculated field like second column:

sum( {$<Month_ID = {$(=Max(Month_ID)-1)>}Amount)

But the resul is that it shows only amount for November and all the other are zero, instead i would like to obtain a table with for example on April i can see 17 for second column because the amount of the previous month are amount of march, and this for all the other rows

How can I get it?

Andrea

Re: Current vs Previous

sergio0592 — Fri, 04 Aug 2017 22:23:51 GMT

Use :

above(sum(Amount))

or create a variable v_previous=Month_ID-1 and use in your set analysis as

sum({<Month_ID={$(v_previous)}>} Amount)

Re: Current vs Previous

andrea90casa — Fri, 04 Aug 2017 22:50:13 GMT

Hi Jean-Baptiste

Thanks for your answer.

The first solution is ok but give to me problem when i want at example compare amount of Jan 2017 with amount of Dec 2016.

The second one don't give to me any result

I share a picture of the output

Re: Current vs Previous

sasiparupudi1 — Sat, 05 Aug 2017 05:14:02 GMT

May be like this?

Re: Current vs Previous

andrea90casa — Sat, 05 Aug 2017 06:02:06 GMT

Hi Sasidhar

Could you share me the formula you have used? because with PE i can't open your file.

Andrea

Re: Current vs Previous

sasiparupudi1 — Sat, 05 Aug 2017 06:29:43 GMT

May be try the following script

x:
Load Month_ID,Date,Month,Amount,
Year(Date) as Year;
Load * Inline
[
Month_ID,Date,Month,Amount
12,1/12/2016,Dec,30
1,1/1/2017,Gen,20
2,1/2/2017,Feb,37
3,1/3/2017,Mar,17
4,1/4/2017,Apr,50
5,1/5/2017,May,14
6,1/6/2017,Jun,22
7,1/7/2017,Jul,18
8,1/8/2017,Ago,10
9,1/9/2017,Sep,25
10,1/10/2017,Oct,24
11,1/11/2017,Nov,13
12,1/12/2017,Dec,19

];
Final:
NoConcatenate Load Month_ID,Date,Year,Month,Amount,
Previous(Amount) as PreviousAmount
Resident x
Order by Year,Date Desc;

Drop Table x;

Re: Current vs Previous

effinty2112 — Sat, 05 Aug 2017 06:57:30 GMT

Hi Andrea,

Add the expression shown here to a straight table:

Month_ID	Date	Month	Sum(Amount)	Above(TOTAL Sum(Amount))
1	01/01/2017	Gen	20
2	01/02/2017	Feb	37	20
3	01/03/2017	Mar	17	37
4	01/04/2017	Apr	50	17
5	01/05/2017	May	14	50
6	01/06/2017	Jun	22	14
7	01/07/2017	Jul	18	22
8	01/08/2017	Ago	10	18
9	01/09/2017	Sep	25	10
10	01/10/2017	Oct	24	25
11	01/11/2017	Nov	13	24
12	01/12/2017	Dec	19	13

Cheers

Andrew

Re: Current vs Previous

andrea90casa — Sat, 05 Aug 2017 07:10:42 GMT

Hi Andrew,

But this is works even if i have data for two years and i want to select only one (ex° 2017)? Because I would like to compare even Jan 2017 with Dec 2016 for example.

I tried to create Month_ID Because, for example, Dec 2017 has 12 as Month_ID and Jan 2018 has 13.

But I can't create a Set Analysis that works in this situation and i don't know where is the problem

Andrea

Re: Current vs Previous

andrea90casa — Sat, 05 Aug 2017 07:15:28 GMT

I founda sort of solution using set analysis but the problem is that when i create a bar chart i can see only one month (which i select with Month Field), instead i would like to see all the twelve month at the same time with value of the previous month on each bar.

Re: Current vs Previous

effinty2112 — Sat, 05 Aug 2017 07:17:36 GMT

Hi Andrea,

The straight table I showed had two expressions modify them to

Sum({$<Year, Month>}Amount)

and

Above(TOTAL Sum({$<Year,Month>}Amount))

and the expressions will disregard selections made on the fields year and month, so if you select on these fields you should see the rows you need.

Cheers

Andrew

Re: Current vs Previous

andrea90casa — Sat, 05 Aug 2017 07:25:57 GMT

Hi Andrew

Yes with this formula it works but digregarding the selection on years if I collect data for two years it shows to me 23 months and not only the month of the Year selected (Annual report).

Andrea

Re: Current vs Previous

antoniotiman — Sat, 05 Aug 2017 08:07:32 GMT

Hi Andrea,

it would be better to use As-OfTable technique

https://community.qlik.com/blogs/qlikviewdesignblog/2015/11/02/the-as-of-table#comment-49905

see exemple

Table:
Load Date,Amount Inline [
Month_ID,Date,Month,Amount
12,1/12/2016,Dec,30
1,1/1/2017,Gen,20
2,1/2/2017,Feb,37
3,1/3/2017,Mar,17
4,1/4/2017,Apr,50
5,1/5/2017,May,14
6,1/6/2017,Jun,22
7,1/7/2017,Jul,18
8,1/8/2017,Ago,10
9,1/9/2017,Sep,25
10,1/10/2017,Oct,24
11,1/11/2017,Nov,13
12,1/12/2017,Dec,19];

Calendar:
Load Date,MonthName(Date) as MonthName,
Month(Date) as Month_ID,Year(Date) as Year
Resident Table;

TmpAsOfMonth:
LOAD DISTINCT MonthName Resident Calendar;
Join (TmpAsOfMonth) Load
MonthName as AsOfMonth
Resident TmpAsOfMonth;

AsOfCalendar:
Load MonthName,
AsOfMonth,
Round((AsOfMonth-MonthName)*12/365.2425) as MonthDiff,
Year(AsOfMonth)-Year(MonthName) as YearDiff
Resident TmpAsOfMonth
Where AsOfMonth >= MonthName;

Drop Table TmpAsOfMonth;

In Front End

Dimension AsOfMonth

Expression

Sum({<MonthDiff={0}>} Amount)

Sum({<MonthDiff={1}>} Amount)

Regards,

Antonio