Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
tsachdev473331
New Contributor

selected year vs Previous year

which syntax would be correct for selected year sales, i am getting same output.

 

Sum({<Year= {$(=(year))}>}Sales)

or 

Sum({<Year= {$(=only(year))}>}Sales)

 

1 Solution

Accepted Solutions
Partner
Partner

Re: selected year vs Previous year

 

Need more info on how your visualization looks to help properly

let me give a shot and hope i dont confuse the hell out of you 🙂

 

 

 

scenario 1 - Supposing you want to show selected year vs previous year

current year expression  - Sum({<Year= {$(=only(year))}>}Sales)

previous year expression - Sum({<Year= {$(=only(year)-1)}>}Sales)

but using only() when more than 1 possible value exists (i.e. in this case no selection is made on year filter) will return null i.e. your expression returns null or 0

 

scenario 2 - same as above but if nothing is selected you want to current (max) year vs previous year

current year expression  - Sum({<Year= {$(=max(year))}>}Sales)

previous year expression - Sum({<Year= {$(=max(year)-1)}>}Sales)

            i.e. if you have data from 2012-2019 by default you show 2019 vs 2018. if user selects year 2017. you show 2017 vs 2016

hope its clear

View solution in original post

7 Replies
Partner
Partner

Re: selected year vs Previous year

selected Year will be given by default.
Why do you need set analysis?
Both should give same output but will work only if a single year is available.
Partner
Partner

Re: selected year vs Previous year

Rule of thumb:

Typing a field name, MyField, in an expression is equivalent to Only(MyField). 

Sum({<Year= {$(=(year))}>}Sales) is the same as Sum({<Year= {$(=only(year))}>}Sales)

Sum(Sales) + ExchangeRate is the same as  Sum(Sales) + only(ExchangeRate) 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
tsachdev473331
New Contributor

Re: selected year vs Previous year

what if year field has 5-6 distinct values and user do selection to compare with previous year sales
Partner
Partner

Re: selected year vs Previous year

 

Need more info on how your visualization looks to help properly

let me give a shot and hope i dont confuse the hell out of you 🙂

 

 

 

scenario 1 - Supposing you want to show selected year vs previous year

current year expression  - Sum({<Year= {$(=only(year))}>}Sales)

previous year expression - Sum({<Year= {$(=only(year)-1)}>}Sales)

but using only() when more than 1 possible value exists (i.e. in this case no selection is made on year filter) will return null i.e. your expression returns null or 0

 

scenario 2 - same as above but if nothing is selected you want to current (max) year vs previous year

current year expression  - Sum({<Year= {$(=max(year))}>}Sales)

previous year expression - Sum({<Year= {$(=max(year)-1)}>}Sales)

            i.e. if you have data from 2012-2019 by default you show 2019 vs 2018. if user selects year 2017. you show 2017 vs 2016

hope its clear

View solution in original post

tsachdev473331
New Contributor

Re: selected year vs Previous year

Thanks..just one more info, In scenario 1, In what case sales value can be
more than 1
Partner
Partner

Re: selected year vs Previous year

e.g. No selection is made on year filter
Partner
Partner

Re: selected year vs Previous year

basically scenrio 1 and 2 is the same apart from error handling where more than 1 year is possible