Current vs Previous

[andrea90casa]

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

[andrea90casa]

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

[antoniotiman · Accepted solution]

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