Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

7 Replies
Highlighted
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
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

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