Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Calculating total

I'm working on an app which has the following fields: Functional Area, Sub Functional Area and 3 different costs- Actuals (eUSD_Amt), budget(bUSD_Amt) and outlook (oUSD_Amt).

I have data for all 12 months of 2017 for outlook. But for Actuals, I have data till July only.

I have to show the sum of Actuals for all the 12 months. Since I have data only till July, I need to add the corresponding months' outlook data for missing months.

Something like this:

Jan(eUSD_Amt)+ Feb(eUSD_Amt) + Mar(eUSD_Amt)+ Apr(eUSD_Amt)+ May(eUSD_Amt)+ Jun(eUSD_Amt)+ Jul(eUSD_Amt)+ Aug(oUSD_Amt)+ Sep(oUSD_Amt)+ Oct(oUSD_Amt)+ Dec(oUSD_Amt)

We would be getting the remaining months Actuals (eUSD_Amt) in due course of the remaining part of the year. Till then, I need to use the outlook (oUSD_Amt) for unavailable months' data to calculate the total Actuals for entire 12 months.

I hope I have made my requirement clear.

Thank you,

Yash

1 Solution

Accepted Solutions
MVP

May be this

=Sum({<Year = {\$(=Max(Year))}>}Aggr(If(Sum(eUSD_Amt) = 0, Sum(oUSD_Amt), Sum(eUSD_Amt)), Year, Month))

10 Replies

Try this

RangeSum(sum({<monthfield ={'jan','feb','mar','apr','may','jun'}>} TOTAL eUSD_amt), sum({<monthfield = {'jul','aug','sep','oct','dec'}>} TOTAL oUSD_amt))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Partner - Creator III

Hi, in your example the actuals for future months is always 0 which you can use.

This expression should work:

if(Sum(eUSD_Amt) > 0, Sum(eUSD_Amt), sum(oUSD_Amt))

jf you set a color expression with the same rules you can differentiate visually also on if you are looking at an actual or a forecast.

Contributor III
Author

It did not work.

MVP

May be this

=Sum({<Year = {\$(=Max(Year))}>}Aggr(If(Sum(eUSD_Amt) = 0, Sum(oUSD_Amt), Sum(eUSD_Amt)), Year, Month))

I am using mobile now? Just guessing? Looks like sunny share the image.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Champion III

Or may be just this!

=sum(AGGR(if(sum(eUSD_Amt)=0,sum(oUSD_Amt),sum(eUSD_Amt)),Year,Month))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
MVP

The total will be incorrect unless the OP is willing to switch to straight table

Contributor III
Author

Thank you...that worked!

Contributor III
Author

Hi Sunny,

What would be the expression if I want to show the actual amount for individual months?

For example, if Actual data is not available for the month of January, it should show the corresponding Outlook Amount.

I have a new requirement where in along with Actual total, I need to show the individual months in text boxes.

Thanks,

Yash

Community Browser