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: 
tsachdev473331
Contributor II
Contributor II

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)

 

Labels (1)
1 Solution

Accepted Solutions
dplr-rn
Partner - Master III
Partner - Master III

 

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
dplr-rn
Partner - Master III
Partner - Master III

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.
Vegar
MVP
MVP

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) 

tsachdev473331
Contributor II
Contributor II
Author

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

 

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

tsachdev473331
Contributor II
Contributor II
Author

Thanks..just one more info, In scenario 1, In what case sales value can be
more than 1
dplr-rn
Partner - Master III
Partner - Master III

e.g. No selection is made on year filter
dplr-rn
Partner - Master III
Partner - Master III

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