Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Xijamk
New 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

Re: Last Year Sales with Year as Dimension

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

Example file is also attached.

Re: Last Year Sales with Year as Dimension

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

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

Re: Last Year Sales with Year as Dimension

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

Example file is also attached.

Partner
Partner

Re: Last Year Sales with Year as Dimension

Hi,

You can try this

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

MVP
MVP

Re: Last Year Sales with Year as Dimension

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
Xijamk
New Contributor

Re: Last Year Sales with Year as Dimension

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.

Re: Last Year Sales with Year as Dimension

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

Xijamk
New Contributor

Re: Last Year Sales with Year as Dimension

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

Re: Last Year Sales with Year as Dimension

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?

Xijamk
New Contributor

Re: Last Year Sales with Year as Dimension

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

ql.PNG

 

Re: Last Year Sales with Year as Dimension

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

Capture.PNG