How to create running budget remaining

[#1]

I have an expense report that lists expenses by account. Each account has an associated budget. How can I create a running 'budget remaining' column? An example of what I'd like to get is:

Account    Budget    Expense Item    Expense Amount     Budget Remaining

1234       $4,000    XYZ               $1,000               $3,000

1234       $4,000    ZYX               $1,500               $1,500

2345       $5,000    ABC               $2,000               $3,000

2345       $5,000    BCD               $1,250               $1,750

...

[vikasmahajan]

PFA

Solution.

Vikas

[Anonymous]

Take a straight/pivot table....

Dimension: Account, Expense Item

Expression:

Budget: sum(Budget)

ExpenseAmount: sum(ExpenseAmount)

Remaining Budget:   sum(Budget)-sum(ExpenseAmount)

or simply

column(3)-column(4)

[vikasmahajan]

Can you attach qvw with your solution what you are trying to explain ??

Vikas

[vikasmahajan]

Also compare your results with expected o/p with user.

[Anonymous]

I am explaining what rcaliari has asked.

[#7]

Hi Rcaliari,

In your load script, add this code under your columns. For example

Load [Account],

[Budget],

[Expense Amount],

...........

[Budget]-[Expense Amount] as [Budget Remaining]

from TableName;

Regards,

Rohan

[#8]

I don't believe this can be done in a load script since the budgets and expenses are contained in different files with one budget entry for each account, and multiple expense items.

[#9]

This doesn't give a running budget remaining... it treats each line separately, so they are all being evaluated against the original budget instead of taking into account prior expenses.

[#10]

I think I need to be using the aggr function, but it only gives me one line that has the final budget remaining, not the budget remaining after each expense... I am computing Budget Remaining as

    Budget - aggr(sum([Expense Amount]), Account)