Calculating total

[yashcena]

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

[Anil_Babu_Samineni]

Try this

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

[niclaz79]

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.

[yashcena]

It did not work.

[sunny_talwar · Accepted solution]

May be this

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

[Anil_Babu_Samineni]

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

[vinieme12]

Or may be just this!

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

[sunny_talwar]

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

[yashcena]

Thank you...that worked!

[yashcena]

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