Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
try this
sum({<DYear=>}Profit) - sum({<DLastYear=>}Profit)
also try this
sum(if(DYear,Profit)) - sum(if(DLastYear,Profit))
try this
sum(total <DYear>Profit)-sum(total <DLastYear>Profit)
or
sum({$<DYear=>}Profit) - sum({$<DLastYear=>}Profit)
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?
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.
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
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.
I've attached it for you to see then. I need Sum(Profit in DYear) - Sum(Profit in DLastYear) = answer
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)