5 Replies Latest reply: Dec 19, 2017 4:42 AM by Sunny Talwar

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

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!

• ###### Re: How to sum up the value of two rows in one table during data transform

Try this

Table:

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:

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;

• ###### Re: How to sum up the value of two rows in one table during data transform

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

• ###### Re: How to sum up the value of two rows in one table during data transform

Got it, try this

Table:

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)

budge_type,

year,

FirstSortedValue(budge, month) as MinDateBudge

Resident Table

Group By [cost center], budge_type, year;

FinalTable:

MinDateBudge- budge as Budget_Final

Resident Table;

DROP Table Table;

• ###### Re: How to sum up the value of two rows in one table during data transform

May be try somethihg like

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

• ###### Re: How to sum up the value of two rows in one table during data transform

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)