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

[sergio0592]

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)

[andrea90casa]

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

[sasiparupudi1]

May be like this?

[andrea90casa]

Hi Sasidhar

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

Andrea

[sasiparupudi1]

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;

[effinty2112]

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

[andrea90casa]

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

[andrea90casa]

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.

[effinty2112]

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