Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Tags (3)
15 Replies
Not applicable

Re: Sum(Profit) - last year

try this

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

Not applicable

Re: Sum(Profit) - last year

also try this

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

er_mohit
Honored Contributor II

Re: Sum(Profit) - last year

try this

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

or

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

Not applicable

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?

MVP
MVP

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.

Not applicable

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

MVP
MVP

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.

Not applicable

Re: Sum(Profit) - last year

qvprob.jpg

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

MVP
MVP

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)

Community Browser