12 Replies Latest reply: Aug 8, 2012 8:34 AM by Deepak Vadithala

# Showing results from last year

I have the following table:

(The first two columns belong to CAFE and the other two to RESTAURANT)

2011201220112012
Earnings (No taxes)\$3,000,000\$3,600,000\$2,900,000\$3,000,000
Earnings (With taxes)\$2,300,000\$3,000,000\$2,500,000\$2,550,000
Spent\$1,600,000\$2,000,000\$2,100,000\$2,000,000
Benefit\$700,000\$1,000,000\$400,000\$550,000

I would like to include three new rows so I can see the benefit last year, the difference in both benefits and the % increase (or decrease).

It would look something like this:

(The first two columns belong to CAFE and the other two to RESTAURANT)

2011201220112012
Earnings (No taxes)\$3,000,000\$3,600,000\$2,900,000\$3,000,000
Earnings (With taxes)\$2,300,000\$3,000,000\$2,500,000\$2,550,000
Spent\$1,600,000\$2,000,000\$2,100,000\$2,000,000
Benefit\$700,000\$1,000,000\$400,000\$550,000
Last year's benefit                -\$700,000                -\$400,000
Difference                -\$300,000                -\$150,000
%increase                -42.86%                -37.5%

The problem I have is that if I can't get to show the benefit from an earlier year in the column from a different year. This example illustrates the problem I'm having.

(The first two columns belong to CAFE and the other two to RESTAURANT)

2011201220112012
sum(<year={'2012'}> Earnings)                -\$3,600,000           -\$3,000,000

I haven't found a solution yet. If you can think of something please let me know.

• ###### Re: Showing results from last year

Hi,

Rather than set analysis, in this situation you need to use Chart Inter Record Functions - namely the BEFORE() function, which returns the expression results for the preceeding dimension.

See the attached qvw and see if that works for you.

Regards

Azam

• ###### Re: Showing results from last year

Ok, this works as long as I maintain both years in the table, but what if I wanted to see only 2012? Is there any way to still see the Last year's benefit

• ###### Re: Showing results from last year

Hi,

Yes, you’re right, you can only ‘see’ other dimensions if they are selected. I am not aware of any way of modifying this behaviour... sorry I can’t be more help.

Regards,

Azam

• ###### Re: Showing results from last year

How about attached approach? Let me know if this makes sense...

Cheers,

DV

http://QlikShare.com

• ###### Re: Showing results from last year

Sorry, I'm currently using personal edition since my license doesn't validate correctly, so I can't open the document.

• ###### Re: Showing results from last year

No problem.

Use the below expression as variable definition…

vBenefit

=sum({1<Year = {2011}, Establishment = {'Cafe'}>} [Earnings (With Taxes)]) - sum({1<Year = {2011}, Establishment = {'Cafe'}>} Spent)

Make sure to include the “=” (equals to) at the beginning of the expression while defining the variable.

Then within your chart expression use the below expression :

=IF(Year = 2012, \$(vBenefit), NULL)

Let me know if this works…

Cheers,

DV

www.QlikShare.com

• ###### Re: Showing results from last year

Wouldn't that give the same result for both cafe and restaurant?

• ###### Re: Showing results from last year

Sorry, you are right…however, temporary solution is to have multiple IF statements and variables.

IF(Year = 2012 AND Establishment = 'Cafe', vCafe, IF(Year = 2012 AND Establishment = 'Restaurant', vRestaurant,
NULL))

vRestaurant : “=sum({1<Year = {2011}, Establishment = {'Restaurant'}>} [Earnings (With Taxes)]) - sum({1<Year = {2011}, Establishment = {'Restaurant'}>} Spent)

vCafe : “=sum({1<Year = {2011}, Establishment = {'Cafe'}>} [Earnings (With Taxes)]) - sum({1<Year = {2011}, Establishment = {'Cafe'}>} Spent)

This is not ideal solution but it works for now. I understand you might have more than 2 establishments and it’s not ideal to hard code them. But I’ll come back to you with more robust solutions meanwhile you can have something to use…

Cheers,

DV

• ###### Re: Showing results from last year

This works but it can be a bit tedious since, as you correctly guessed, I have many other establishments.

As a temporary solution I removed the year as a dimension and I'm always showing 2012 (so previous year can be set to 2011). Anyways, if you come across anything that could be helpful I'll appreciate any further information.

Thank you anyways for your time and help.

• ###### Re: Showing results from last year

You’re welcome. I’ve solved this one in the past. I’ll come back to you with one expression which can be used across multiple dimensions. Hopefully in next two days…

Cheers,

DV

www.QlikShare.com

• ###### Re: Showing results from last year

=aggr(above(Sum({<Year>}[Earnings (With Taxes)]) - Sum({<Year>}Spent)),Establishment,Year)

as expression for Last Year's Benefit, and then

=Benefit - "Last Year's Benefit"

=(Benefit - "Last Year's Benefit") / "Last Year's Benefit"

for Difference / Percentage?

Regards,

Stefan

• ###### Re: Showing results from last year

Stefan - This is clever stuff. I've tried the Aggr() function but didn't realise that I can use Above(). Totally makes sense.

Thank you.

Cheers,

DV