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: 
vanand3535
Creator III
Creator III

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
MK9885
Master II
Master II

=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

View solution in original post

20 Replies
rangam_s
Creator II
Creator II

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

Try this.

Chanty4u
MVP
MVP

may be this

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

vanand3535
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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

MK9885
Master II
Master II

Maybe use


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

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

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

pedrolyra
Contributor III
Contributor III

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Thanks for reply..

Let me test this syntax.

MK9885
Master II
Master II

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