15 Replies Latest reply: Nov 27, 2013 11:27 PM by Tresesco B

# Sum(Profit) - last year

Hello,

Simply. I have fields "Profit"    "DYear"    and   "DLastYear"

I need to calculate sum(profit) of Dyear  -minus-   sum(profit) of DlastYear

I don't know how to calculate sum(profit) in a period,  e.g  sum(profit in Dyear)  and that's my problem

All help very appreciated

• ###### Re: Sum(Profit) - last year

try this

sum({<DYear=>}Profit) - sum({<DLastYear=>}Profit)

• ###### Re: Sum(Profit) - last year

also try this

sum(if(DYear,Profit)) - sum(if(DLastYear,Profit))

• ###### Re: Sum(Profit) - last year

try this

sum(total <DYear>Profit)-sum(total <DLastYear>Profit)

or

sum({\$<DYear=>}Profit) - sum({\$<DLastYear=>}Profit)

• ###### Re: Sum(Profit) - last year

They all reply "No Data to display"  except for the last one sum({\$<DYear=>}Profit) - sum({\$<DLastYear=>}Profit) which displays the incorrect information.    DLast Year is       Year(DDate) - 1 as DLastYear,   is that right ?
It definitely shows up as 2012 if I select 2013 .  I just have no idea how to sum profit in year, and sum(profit) in last year.  Is it really difficult?

• ###### Re: Re: Sum(Profit) - last year

PFA

Expression:

=Sum({<Year={"\$(=Max(Year)-1)"}>}Sales)   // previous year

=Sum({<Year={"\$(=Max(Year))"}>}Sales)      // current year

Update:

It is a generic solution, i went through your post once again and found that it might not help you. If so, please share a sample app.

• ###### Re: Sum(Profit) - last year

It doesn't work, really sorry.
The app connects to a huge database and I'm a bit inexperienced..
Kindly create

year 1

company          Profit
test1               6500.00
test2               4500.00

year 2

company          Profit

test1               5000.00

test2               3700.00

Year 3

company          Profit

test1               4800.00

test2               4000.00

The Periods are
Year:
1

2
3

Select "Year 3" and outcome is

company          Profit

test1               4800.00

test2               4000.00

I want to calculate
"Year 3 - Year 2"

company          Profit

test1               -200.00

test2                300.00

So I somehow need to calculate    Profit(selectedyear) - Profit(selected year-1) = -200   , in year 3 - year 2

• ###### Re: Re: Sum(Profit) - last year

See, the data presentation matters. How does the actual data look like?

=Sum({<Year={"\$(=Max(Year))"}>}Sales) - Sum({<Year={"\$(=Max(Year)-1)"}>}Sales)

with the data i have taken in sample file, you would get the out put in a text box with the above expression.

• ###### Re: Sum(Profit) - last year

I've attached it for you to see then.  I need   Sum(Profit in DYear) - Sum(Profit in DLastYear) =  answer

• ###### Re: Re: Sum(Profit) - last year

I would suggest you to simplify your model a bit. Creat a field Year in the script like:

Year(DDate) as Year                 // DYear, DLastYear might not be required.

Then use the above expression after a small tweak like:

Sum({<Year={"\$(=Max(Year))"}>}Profit) - Sum({<Year={"\$(=Max(Year)-1)"}>}Profit)

If you don't want to modify the script, try this:

Sum({<Year={"\$(=Max(DYear))"}>}Profit) - Sum({<Year={"\$(=Max(DYear)-1)"}>}Profit)

• ###### Re: Sum(Profit) - last year

"no data to display"   if my script is modified or unmodified using the second one. So, still not working
Ah...Thanks for your help so far though!

• ###### Re: Re: Sum(Profit) - last year

Hi tresesco...

could you please explain {"\$ (= Max (Year))"}, obvious that Max (Year) is the biggest year of available data,

but I do not understand the \$ symbol does?

double quotes make a difference between {"\$ (= Max (Year))"} or {\$ (= Max (Year))} latter becomes gray in the formula?

• ###### Re: Re: Re: Sum(Profit) - last year

Hi,

It is simple dollar sign expansion used along with set analysis . For better understanding, see page17,18 of the attached doc.

• ###### Re: Sum(Profit) - last year

Any other ideas?

• ###### Re: Sum(Profit) - last year

Any ideas?

• ###### Re: Sum(Profit) - last year

Hi Darren,

Please share a sample app. If you are worried about the data security, you can always scramble the data and upload.