Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

15 Replies
Not applicable
Author

try this

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

Not applicable
Author

also try this

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

er_mohit
Master II
Master II

try this

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

or

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

Not applicable
Author

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?

tresesco
MVP
MVP

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.

Not applicable
Author

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

tresesco
MVP
MVP

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.

Not applicable
Author

qvprob.jpg

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

tresesco
MVP
MVP

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)