topic Re: Sum(Profit) - last year in QlikView

Sum(Profit) - last year

Tue, 03 Sep 2013 07:36:20 GMT

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

Tue, 03 Sep 2013 07:43:25 GMT

try this

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

Re: Sum(Profit) - last year

Tue, 03 Sep 2013 07:45:00 GMT

also try this

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

Re: Sum(Profit) - last year

er_mohit — Tue, 03 Sep 2013 07:49:24 GMT

try this

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

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

Re: Sum(Profit) - last year

Tue, 03 Sep 2013 08:47:01 GMT

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

tresB — Tue, 03 Sep 2013 09:06:13 GMT

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

Tue, 03 Sep 2013 09:19:13 GMT

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

tresB — Tue, 03 Sep 2013 09:27:35 GMT

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

Tue, 03 Sep 2013 09:41:00 GMT

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

tresB — Tue, 03 Sep 2013 09:51:15 GMT

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

Tue, 03 Sep 2013 10:27:21 GMT

"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: Sum(Profit) - last year

Tue, 03 Sep 2013 11:19:24 GMT

Any other ideas?

Re: Sum(Profit) - last year

Mon, 09 Sep 2013 06:22:13 GMT

Any ideas?

Re: Sum(Profit) - last year

tresB — Mon, 09 Sep 2013 06:32:10 GMT

Hi Darren,

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

Re: Re: Sum(Profit) - last year

Anonymous — Wed, 27 Nov 2013 21:56:37 GMT

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?

I thank you in advance!

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

tresB — Thu, 28 Nov 2013 04:27:38 GMT

Hi,

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