Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vanand3535
Contributor

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?


1 Solution

Accepted Solutions
Khan_Mohammed
Honored Contributor II

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

20 Replies
rangam_s
Contributor II

Re: last year data in Qlik Sense using expression

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

Try this.

sureshqv
Esteemed Contributor III

Re: last year data in Qlik Sense using expression

may be this

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

vanand3535
Contributor

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

vanand3535
Contributor

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

Khan_Mohammed
Honored Contributor II

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)

pedrolyra
New Contributor III

Re: last year data in Qlik Sense using expression

Hey Vivek, try something like this

SUM({$<SeasonDate= {"<=$(=date(makedate(year(addmonths(max(SeasonDate),-12)),12,31)))"}>} Value)

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

att

vanand3535
Contributor

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?

vanand3535
Contributor

Re: last year data in Qlik Sense using expression

Thanks for reply..

Let me test this syntax.

Khan_Mohammed
Honored Contributor II

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

Community Browser