topic Re: Set analysis calculate difference between years of Sales in App Development

Set analysis calculate difference between years of Sales

Arianna-lab — Tue, 07 Jun 2022 09:18:19 GMT

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! 🙂

Re: Set analysis calculate difference between years of Sales

justISO — Tue, 07 Jun 2022 09:51:51 GMT

Hi, have you tried something like this?:

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

Re: Set analysis calculate difference between years of Sales

saranyadurai — Tue, 07 Jun 2022 09:55:45 GMT

Hi @Arianna-lab ,

May be try like this,

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

Re: Set analysis calculate difference between years of Sales

Arianna-lab — Tue, 07 Jun 2022 09:57:45 GMT

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).

Re: Set analysis calculate difference between years of Sales

maxgro — Tue, 07 Jun 2022 10:05:24 GMT

You can find some examples

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/ChartFunctions/SetAnalysis/set-analysis-expressions.htm

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?attachment-id=89393