Reference Column in Pivot for % in rest of Pivot?

110 — Fri, 04 Jul 2025 11:18:41 GMT

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;

Re: Reference Column in Pivot for % in rest of Pivot?

marcus_sommer — Fri, 04 Jul 2025 11:46:12 GMT

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

topic Reference Column in Pivot for % in rest of Pivot? in App Development

Reference Column in Pivot for % in rest of Pivot?

Re: Reference Column in Pivot for % in rest of Pivot?