Showing results from last year

[#1]

I have the following table:     

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

	2011	2012	2011	2012
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)

	2011	2012	2011	2012
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)

	2011	2012	2011	2012
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.

[a_mullick]

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

[#3]

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

[a_mullick]

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

[IAMDV]

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

Cheers,

DV

http://QlikShare.com

[#6]

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

[IAMDV]

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

[#8]

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

[IAMDV]

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

[#10]

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.