Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
...
PFA
Solution.
Vikas
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)
Can you attach qvw with your solution what you are trying to explain ??
Vikas
Also compare your results with expected o/p with user.
I am explaining what rcaliari has asked.
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
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.
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.
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)