topic Re: Compare year vs last year sales in App Development

Compare year vs last year sales

Tue, 06 Dec 2016 14:46:34 GMT

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

Re: Compare year vs last year sales

sunny_talwar — Tue, 06 Dec 2016 15:47:38 GMT

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

Re: Compare year vs last year sales

Tue, 06 Dec 2016 16:20:56 GMT

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.

Re: Compare year vs last year sales

sunny_talwar — Tue, 06 Dec 2016 16:37:12 GMT

feccbt is a year or date field?

Re: Compare year vs last year sales

Tue, 06 Dec 2016 16:55:31 GMT

a date field in sql server

Re: Compare year vs last year sales

sunny_talwar — Tue, 06 Dec 2016 17:30:07 GMT

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

Re: Compare year vs last year sales

Tue, 06 Dec 2016 18:21:28 GMT

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

Re: Compare year vs last year sales

sunny_talwar — Tue, 06 Dec 2016 18:33:47 GMT

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

Re: Compare year vs last year sales

Wed, 07 Dec 2016 02:50:49 GMT

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