Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Last Year Sales with Year as Dimension

Hi, I need to show Last Year Sales per Year, but I'm always end with the same result :

 

1.PNG

And I need this :

2.PNG

 

Sales :  Sum(Sales)

LY Sales :  Sum({<Year={"$(=Date(Year-1,'YYYY'))"}>} Sales)

 

Thanks for any advice.

Labels (2)
2 Solutions

Accepted Solutions
Highlighted

The following should work:
if(match(Year, concat(distinct Year, ',')), before(sum({<Year=>}Sales)))

Example file is also attached.

View solution in original post

Highlighted

Oh yes, my bad, forgot to change the expression... try this expression

Aggr(Above(Sum({<Year=>}Sales)), Country, Year)

View solution in original post

20 Replies
Highlighted

The following should work:
if(match(Year, concat(distinct Year, ',')), before(sum({<Year=>}Sales)))

Example file is also attached.

View solution in original post

Highlighted
Partner
Partner

Hi,

You can try this

rangesum(before(TOTAL sum(Sales),1,ColumnNo(TOTAL)))

Highlighted

Use these expressions rather:

 

Sales:     =Sum({<Year = {$(=Max(Year))}>} Sales)
LY Sales:  =Sum({<Year = {$(=Max(Year) - 1)}>} Sales)

 

Max(Year) will respond to your selections and will get the latest year and prior year when nothing is selected. 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Contributor
Contributor

Thanks for the responses!

Nicole, this seems to work, but if I select Year 2018 I can't hide 2016 and 2017 and I need to see just the selected years.

Reon, your solution is not working.

Jonty, your solution is not working if I need to see 2 years or more, and the last year of that years.

Highlighted

You want to see 2016 & 2017 when 2018 is selected or do you not want to see it?

Highlighted
Contributor
Contributor

I don't want to see it.

If I select 2018, I want to see:

2018.PNG

If I select 2018 and 2017, I want to see:

20182017.PNG

If I select 2017 and 2016, I want to see:

20172016.PNG

Highlighted

That is what @Nicole-Smith proposed solution is doing, isn't it? I mean she only used 2 years in the data that she created, but if there were more than 2 years, it would work the same way....

When 2018 is selected

image.png

When nothing is selected

image.png

What's wrong here?

Highlighted
Contributor
Contributor

I don't know why this works differently on your end, but I'm seeing this : 

ql.PNG

 

Highlighted

Do you have 'Suppress Zero-Values' on the presentation tab of the chart properties?

Capture.PNG