20 Replies Latest reply: Jun 21, 2018 3:26 PM by Vivek Anand

# last year data in Qlik Sense using expression

Hi all,

I need to find the Sales in prior season .

Season= 2013,2014,2015,2016,2016

Logic :-

Prior season sales(2016)=  total sales(2013,2014,2015)

Prior season sales(2015)=total sales(2013,2014)

same for others...

How can I do this?

• ###### Re: last year data in Qlik Sense using expression

sum({<Season={"<\$(=Max(Season))"}>}Sales)

Try this.

• ###### Re: last year data in Qlik Sense using expression

I need more than one year..

Ex:

I have "Season" as dimension where values are:-  2013,2014,2015,2016,2017

If I talked for total number of sales in Prior Season of 2017 = Total number of sales in all the year which is less than or earlier to 2017 = total number of sales(2013,2014,2015,2016)

same ,

total number of sales in Prior Season of 2016 =total number of sales(2013,2014,2015)

total number of sales in Prior Season of 2015 =total number of sales(2013,2014)

total number of sales in Prior Season of 2014 =total number of sales(2013)

total number of sales in Prior Season of 2013 = 0(Zero)   Because I have only from  2013-17

• ###### Re: last year data in Qlik Sense using expression

may be this

sum({<Year= {"<\$(=Max(Year))"}} >}Sales)

• ###### Re: last year data in Qlik Sense using expression

I need more than one year..

Ex:

I have "Season" as dimension where values are:-  2013,2014,2015,2016,2017

If I talked for total number of sales in Prior Season of 2017 = Total number of sales in all the year which is less than or earlier to 2017 = total number of sales(2013,2014,2015,2016)

same ,

total number of sales in Prior Season of 2016 =total number of sales(2013,2014,2015)

total number of sales in Prior Season of 2015 =total number of sales(2013,2014)

total number of sales in Prior Season of 2014 =total number of sales(2013)

total number of sales in Prior Season of 2013 = 0(Zero)   Because I have only from  2013-17

• ###### Re: last year data in Qlik Sense using expression

Maybe use

sum({< Year={\$(=(Max(Year))-1)}

>+< Year={\$(=(Max(Year))-2)}

>+< Year={\$(=(Max(Year))-3)}>}Sales)

• ###### Re: last year data in Qlik Sense using expression

Let me test this syntax.

• ###### Re: last year data in Qlik Sense using expression

In your case I guess you should use

sum({< Year={\$(=(Max(Year))-1)}

>+< Year={\$(=(Max(Year))-2)}

>+< Year={\$(=(Max(Year))-3)}

>+< Year={\$(=(Max(Year))-4)}>}Sales)

As you've 4 years excluding 2017

• ###### Re: last year data in Qlik Sense using expression

Sample demo:-

 SEASON SALES PRIOR_SEASON_Sales Calculation hint 2013 10 0 (Sales in 2012 but here is no data for 2012 so it is 0) 2014 20 10(10) (Sales in 2013) 2015 30 30(20+10) (sales in 2014,2013) 2016 40 60(30+20+10) (Sales in 2015,2014,2013) 2017 50 100(40+30+20+10) (Sales in 2016,2015,2014,2013)
• ###### Re: last year data in Qlik Sense using expression

=sum({< Season={\$(=(Max(Season))-1)}

>+< Season={\$(=(Max(Season))-2)}

>+< Season={\$(=(Max(Season))-3)}

>+< Season={\$(=(Max(Season))-4)}>}Sales)

The above expression should give you PRIOR_SEASON_Sales numbers

• ###### Re: last year data in Qlik Sense using expression

I have used this formulae that you told:--

=

Sales({< Season={\$(=(Max(SEASON))-1)}>
+

< Season={\$(=(Max(SEASON))-2)}>

+

< Season={\$(=(Max(SEASON))-3)}>

+

< Season={\$(=(Max(SEASON))-4)}>

+

< Season={\$(=(Max(SEASON))-5)}>

}Sales)

But I'm not able to get the sum sales all previous year for a particular year:-

ex;-

for 2017  I'm not able to get sales of 2016+2015+2014+2013

this formulae is just showing me only data for same year like if sales in 2013 =50

then after applying the formulae sales in 2013 = 50

no changes

• ###### Re: last year data in Qlik Sense using expression

I have used this formulae that you told:--

=

Sum({< Season={\$(=(Max(SEASON))-1)}>
+

< Season={\$(=(Max(SEASON))-2)}>

+

< Season={\$(=(Max(SEASON))-3)}>

+

< Season={\$(=(Max(SEASON))-4)}>

+

< Season={\$(=(Max(SEASON))-5)}>

}Sales)

But I'm not able to get the sum sales all previous year for a particular year:-

ex;-

for 2017  I'm not able to get sales of 2016+2015+2014+2013

this formulae is just showing me only data for same year like if sales in 2013 =50

then after applying the formulae sales in 2013 = 50

no changes

• ###### Re: last year data in Qlik Sense using expression

This is original value without using formulae:-

after applying the formulae:-

=

SUM({< SEASON={\$(=(Max(SEASON))-1)}>
+

< SEASON={\$(=(Max(SEASON))-2)}>

+

< SEASON={\$(=(Max(SEASON))-3)}>

+

< SEASON={\$(=(Max(SEASON))-4)}>

+

< SEASON={\$(=(Max(SEASON))-5)}>

}Sales)

I'm getting output:-

2017 value is missing and it is same as earlier no effect of using this formulae

• ###### Re: last year data in Qlik Sense using expression

I am getting the same data

if sales in 2017 =50 then after applying the formulae

sales in 2017=50

It is not giving the sum of sales in 2016+2015+2014+2013

same as it is

• ###### Re: last year data in Qlik Sense using expression

2017 = 100 won't show up until you have your TOTALS option on in table...

Anyway, thats the only solution I know and its working on my end...

Unless someone else helps you.

• ###### Re: last year data in Qlik Sense using expression

just now i have attached screenshots of the original data please check that

• ###### Re: last year data in Qlik Sense using expression

Sample demo:-

 SEASON SALES PRIOR_SEASON_Sales Calculation hint 2013 10 0 (Sales in 2012 but here is no data for 2012 so it is 0) 2014 20 10(10) (Sales in 2013) 2015 30 30(20+10) (sales in 2014,2013) 2016 40 60(30+20+10) (Sales in 2015,2014,2013) 2017 50 100(40+30+20+10) (Sales in 2016,2015,2014,2013)
• ###### Re: last year data in Qlik Sense using expression

Hey Vivek, try something like this

You cant use this in a table with seasondate as dimension, i think this will work on kpis.

att

• ###### Re: last year data in Qlik Sense using expression

There is no date in my case

my Dimension and values are:-

Season={2013,2014,2015,2016,2017}

ex:- Sum({<.......................>}Sales)

In between how can i use the syntax?

• ###### Re: last year data in Qlik Sense using expression

In your script create a date dimension like this:

date(makedate(num(Season),1,1)) as SeasonDate

this will create a date field for your season

after that use SeasonDate as Dimension