Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
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

Tags (3)
1 Solution

Accepted Solutions

Re: Compare year vs last year sales

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

8 Replies

Re: Compare year vs last year sales

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

Re: Compare year vs last year sales

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

feccbt is a year or date field?

Not applicable

Re: Compare year vs last year sales

a date field in sql server

feccbt.png

Re: Compare year vs last year sales

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

Not applicable

Re: Compare year vs last year sales

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

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

Re: Compare year vs last year sales

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