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

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.

12 Replies
a_mullick
Creator III
Creator III

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

Not applicable
Author

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
Creator III
Creator III

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
Luminary Alumni
Luminary Alumni

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

Cheers,

DV

http://QlikShare.com

Not applicable
Author

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

IAMDV
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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

IAMDV
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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.