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

Compare year vs last year sales

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

VendorsYear selectedYear BeforYear1 - 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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

8 Replies
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))

Not applicable
Author

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?

Not applicable
Author

a date field in sql server

feccbt.png

sunny_talwar

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

Not applicable
Author

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

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

Not applicable
Author

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