Reference Column in Pivot for % in rest of Pivot?

[110]

As usual , a picture is probably the easiest way to explain.

Simply put, we have a year ( 2018 - in green ) where we have some sales figures , we'd like to compare to other years.

The bottom left table is the set analysis that is failing, using code :

(Sum(ExSales)/
Sum({<ExYear={"2018"}>}ExSales)
)
-1

It should look like the figures on the bottom right ( build using load script table ) 

So, if I had access to the load script, I'd do something like this , and simply have a reference to the 2018 numbers for all sites, but sadly , I can't access the load script, so need to do the equivalent in the Chart/Table script at runtime.

NoConcatenate

load
ExSite,
SUM(ExSales) as 'ExSalesFY18'

resident ExampleData

where ExYear =2018

group by ExSite;

[marcus_sommer]

The problem is the year-dimension because the data from 2018 doesn't belong to another year and reverse. It has nothing to do with the selection state respectively the set analysis else the association between the data.

There are various ways to solve such challenge, for example:

• using interrecord-functions like above(), below(), before() and after() to access any cell within the chart
• avoiding the "critical" dimension and using only expressions, like: sum({< Year = {X}>} Value) / sum({< Year = {Y}>} Value)
• implementing adjustments to the data-model, maybe with: The As-Of Table - Qlik Community - 1466130