How to sum up the value of two rows in one table during data transform

[yonghanfi]

Hi, I have a very tricky table like below the green part shows. So when flag is one, the real value of Budge in each month is actually value of budge in current month sum up the budge in January. Anyone can help me achieve the data like in yellow part shows?

Thanks a lot!

cost center	year	month	flag	budge_type	budge	Budget_Final
A	2018	1	1	ADR	65	65
A	2018	2	1	ADR	2	67
A	2018	3	1	ADR	1	66
A	2018	4	1	ADR	0	65
A	2018	5	1	ADR	3	68
A	2018	6	1	ADR	-4	61
A	2018	7	1	ADR	0	65
A	2018	8	1	ADR	2	67
A	2018	9	1	ADR	-2	63
A	2018	10	1	ADR	-1	64
A	2018	11	1	ADR	0	65
A	2018	12	1	ADR	-3	62
B	2019	1	0	Revenue	1000	1000
B	2019	2	0	Revenue	1005	1005
B	2019	3	0	Revenue	1009	1009
B	2019	4	0	Revenue	1003	1003
B	2019	5	0	Revenue	1005	1005
B	2019	6	0	Revenue	1000	1000
B	2019	7	0	Revenue	1002	1002
B	2019	8	0	Revenue	1030	1030
B	2019	9	0	Revenue	1040	1040
B	2019	10	0	Revenue	1050	1050
B	2019	11	0	Revenue	1020	1020
B	2019	12	0	Revenue	1000	1000
B	2019	1	1	ADR	70	70
B	2019	2	1	ADR	2	72
B	2019	3	1	ADR	1	71
B	2019	4	1	ADR	0	70
B	2019	5	1	ADR	3	73
B	2019	6	1	ADR	-4	66
B	2019	7	1	ADR	0	70
B	2019	8	1	ADR	2	72
B	2019	9	1	ADR	-2	68
B	2019	10	1	ADR	-1	69
B	2019	11	1	ADR	0	70
B	2019	12	1	ADR	-3	67

[sunny_talwar]

Try this

Table:

LOAD * INLINE [

    cost center, year, month, flag, budge_type, budge

    A, 2018, 1, 1, ADR, 65

    A, 2018, 2, 1, ADR, 2

    A, 2018, 3, 1, ADR, 1

    A, 2018, 4, 1, ADR, 0

    A, 2018, 5, 1, ADR, 3

    A, 2018, 6, 1, ADR, -4

    A, 2018, 7, 1, ADR, 0

    A, 2018, 8, 1, ADR, 2

    A, 2018, 9, 1, ADR, -2

    A, 2018, 10, 1, ADR, -1

    A, 2018, 11, 1, ADR, 0

    A, 2018, 12, 1, ADR, -3

    B, 2019, 1, 0, Revenue, 1000

    B, 2019, 2, 0, Revenue, 1005

    B, 2019, 3, 0, Revenue, 1009

    B, 2019, 4, 0, Revenue, 1003

    B, 2019, 5, 0, Revenue, 1005

    B, 2019, 6, 0, Revenue, 1000

    B, 2019, 7, 0, Revenue, 1002

    B, 2019, 8, 0, Revenue, 1030

    B, 2019, 9, 0, Revenue, 1040

    B, 2019, 10, 0, Revenue, 1050

    B, 2019, 11, 0, Revenue, 1020

    B, 2019, 12, 0, Revenue, 1000

    B, 2019, 1, 1, ADR, 70

    B, 2019, 2, 1, ADR, 2

    B, 2019, 3, 1, ADR, 1

    B, 2019, 4, 1, ADR, 0

    B, 2019, 5, 1, ADR, 3

    B, 2019, 6, 1, ADR, -4

    B, 2019, 7, 1, ADR, 0

    B, 2019, 8, 1, ADR, 2

    B, 2019, 9, 1, ADR, -2

    B, 2019, 10, 1, ADR, -1

    B, 2019, 11, 1, ADR, 0

    B, 2019, 12, 1, ADR, -3

];

FinalTable:

LOAD *,

If([cost center] = Previous([cost center]) and year  = Previous(year) and budge_type = Previous(budge_type), RangeSum(Peek('Budget_Final'), budge), budge) as Budget_Final

Resident Table

Order By [cost center], budge_type, year, month;

DROP Table Table;

[yonghanfi]

Hi,

Very helpful, thanks a lot! But not exactly what I need. RangeSum can not apply, because final budget is sum of only current month and Month 1. For example for year 2018 and cost center A, final budget is 68 instead of 71.

Br,

Yong

[sunny_talwar]

Got it, try this

Table:

LOAD * INLINE [

    cost center, year, month, flag, budge_type, budge

    A, 2018, 1, 1, ADR, 65

    A, 2018, 2, 1, ADR, 2

    A, 2018, 3, 1, ADR, 1

    A, 2018, 4, 1, ADR, 0

    A, 2018, 5, 1, ADR, 3

    A, 2018, 6, 1, ADR, -4

    A, 2018, 7, 1, ADR, 0

    A, 2018, 8, 1, ADR, 2

    A, 2018, 9, 1, ADR, -2

    A, 2018, 10, 1, ADR, -1

    A, 2018, 11, 1, ADR, 0

    A, 2018, 12, 1, ADR, -3

    B, 2019, 1, 0, Revenue, 1000

    B, 2019, 2, 0, Revenue, 1005

    B, 2019, 3, 0, Revenue, 1009

    B, 2019, 4, 0, Revenue, 1003

    B, 2019, 5, 0, Revenue, 1005

    B, 2019, 6, 0, Revenue, 1000

    B, 2019, 7, 0, Revenue, 1002

    B, 2019, 8, 0, Revenue, 1030

    B, 2019, 9, 0, Revenue, 1040

    B, 2019, 10, 0, Revenue, 1050

    B, 2019, 11, 0, Revenue, 1020

    B, 2019, 12, 0, Revenue, 1000

    B, 2019, 1, 1, ADR, 70

    B, 2019, 2, 1, ADR, 2

    B, 2019, 3, 1, ADR, 1

    B, 2019, 4, 1, ADR, 0

    B, 2019, 5, 1, ADR, 3

    B, 2019, 6, 1, ADR, -4

    B, 2019, 7, 1, ADR, 0

    B, 2019, 8, 1, ADR, 2

    B, 2019, 9, 1, ADR, -2

    B, 2019, 10, 1, ADR, -1

    B, 2019, 11, 1, ADR, 0

    B, 2019, 12, 1, ADR, -3

];

Left Join (Table)

LOAD [cost center],

budge_type,

year,

FirstSortedValue(budge, month) as MinDateBudge

Resident Table

Group By [cost center], budge_type, year;

FinalTable:

LOAD *,

MinDateBudge- budge as Budget_Final

Resident Table;

DROP Table Table;

[qliksus · Accepted solution]

May be try somethihg like

sum({<Month={"1"},flag={"1"}>} total<Year> Budge) + Sum(Budge)

[sunny_talwar]

I wonder why you posted your question in scripting portion when you needed a front end expression. I wasted my precious time . Just kidding....

anyways, is this even giving you what you wanted? I don't think so...

In case you want to get to the write expression, try this

Sum({<month={"1"}>} TOTAL <year, [cost center], budge_type> budge) + Sum({<month = {">1"}>}budge)