Set analysis calculate difference between years of Sales

[Arianna-lab]

Hi everybody,

i have a table with: ProductID, sum(Sales). The field Year works of (2011,2012,2013 e 2014) and I use this field as filter.

For example I would like to add 3 columns:

sum(Sales) of 2013

sum(Sales) of 2012

sum(Sales of (2013-2012)

I'm using the set analysis but I have some problems. Someone can help me? Thank you! 🙂

[justISO]

Hi, have you tried something like this?:

sum({$<Year='2013'>} Sales)

[saranyadurai]

Hi @Arianna-lab ,

May be try like this,

Sum({<Year={$(=Max(Year))}>}Sales) - Sum({<Year={$(=Year(Max(Year)-1))}>}Sales)

[Arianna-lab]

Hi, I try but that specific expression only that year=2013 and applying the filter of other year ( ex. 2012) has no impact.  I need a formula that doesn't specify the year but fits with (2012,2013,2014).  

[maxgro · Accepted solution]

You can find some examples 

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/ChartFunctions/Se...

Year 2021

sum({$<Year={2012}>} Sales)

Returns the sales for the current selection, but with new selections in Year .

Range of years

sum( {$<Year = {“>1978<2004”}>} Sales )

Returns the sales for the current selections, but with a numeric search used to scope the range of years to sum the sales across.

Selected Year -1

sum( {$<Year = {$(#=Only(Year)-1)}>} Sales )

Returns the sales for the previous year in relation to current selection. Here, a dollar-sign expansion is used to calculate previous year.

Max Year -1

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

More details 

https://community.qlik.com/t5/QlikView-Documents/Set-Analysis-syntaxes-examples/ta-p/1491810

https://community.qlik.com/t5/QlikView-Documents/Set-Analysis-syntaxes-examples/ta-p/1491810?attachm...