Compare year vs last year sales

[#1]

In Qlik Sense..

i want to compare the sales of a specific year (selected) with the year before. For example, if i selected 2015 de comparison will be 2015 vs 2014..

Years
2013
2014
2015
2016

Vendors	Year selected	Year Befor	Year1 - Year2
Vendor 1
Vendor 2
Vendor 3

The data is from a db in sql server... year colum name in the db is "feccbt", and the sales colum name is "imptotcbt". To show the total sales i do SUM(imptotcbt).

I can't make that comparison.. Please help me, sorry for my poor english

[sunny_talwar]

May be this:

Year Selected

Sum({<feccbt = {"$(=Max(feccbt))"}>} imptotcbt)

Year Before

Sum({<feccbt = {"$(=Max(feccbt)-1)"}>} imptotcbt)

Year1-Year2

RangeSum(Sum({<feccbt = {"$(=Max(feccbt))"}>} imptotcbt), -Sum({<feccbt = {"$(=Max(feccbt)-1)"}>} imptotcbt))

[#3]

Thanks for answering ...

The problem with that code in the first colum is that is returning me only the sales of the last day registered in the year selected. For example..

For 2015 the last sale is in 30/12/2015... And in the second colum return the sales of the previous day (for 30/12/2015 return 29/12/2015)..

if i use Max(Year(feccbt)) return 0.

[sunny_talwar]

feccbt is a year or date field?

[#5]

a date field in sql server

[sunny_talwar]

Is this resolved now? or still an issue? The reason I ask this is because you have marked correct response

[#7]

The only problem I have is that Max(feccbt) return the day, I need the year. But I don't know how to use  year(feccbt)...

[sunny_talwar · Accepted solution]

Create a year field in the script

LOAD feccbt,

          Year(feccbt) as Year_feccbt,

          OtherField

FROM .....;

and then this:

Year Selected

Sum({<Year_feccbt = {"$(=Max(Year_feccbt))"}, feccbt>} imptotcbt)

Year Before

Sum({<Year_feccbt = {"$(=Max(Year_feccbt)-1)"}, feccbt>} imptotcbt)

Year1-Year2

RangeSum(Sum({<Year_feccbt = {"$(=Max(Year_feccbt))"}, feccbt>} imptotcbt), -Sum({<Year_feccbt = {"$(=Max(Year_feccbt)-1)"}, feccbt>} imptotcbt))

[#9]

Yes, thanks a lot! the information you provide it was very usefull