Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Replace a constant value in Pivot with a Value in excel or Table

I have a pivot table in qlik sense, for which the formulae looks like this…


(0.65) – ((1300)/( SUM({<QUARTER={$(VLQTR)}, $(PSET)>}$(DOLLARS))



I would like to get rid of the numbers but keep those values in an excel sheet and call those columns. So that in future, we can change numbers in excel but not in qlik sense app.

How can we call a fixed value from an excel sheet, it shouldn’t change with any selections !


I am replacing 0.65 with a similar expression like below but not getting any result.




Can anyone help how to replace a fixed number(s) in pivot irrespective of selections or filters?

5 Replies
Creator II
Creator II

You could use a variation of the approach I have used in some apps where all expressions are stored in Excel.

The spreadsheet has two columns - a unqiue ID and the content.  It's loaded as an island table:

FROM [lib://Expressions.xlsx]
(ooxml, embedded labels, table is List);

The expression is:

$(=only({<ExpressionID={'ID1'}>} Expression))


You should be able to store the values in Excel where I expression and embed the expression above as part of your overall expression.


Contributor III
Contributor III

Hi Rogerpegler,

Thank you for the answer.

but your expression is working similar to the expression i had. 

it is displaying result when no selections made.  But if i select specific Business Unit or Location, it is displaying blank.

what i need is , for example:

last Qtr sales = 100

This qtr sales = 50  (ASIA = 20, EU = 15, US = 15)

Now, i entered  the following details in an excel and calling loading Qlik sense appl.



Now i am using below query  to call Last qtr sales:

SUM({1<QTR={'2019Q1'},FLAG={‘Y’}>}DOLLAR)  or

your query $(=only({<QTR={'2019Q1'},FLAG={‘Y’}>} DOLLAR))


DIFFERENCE = 100-50 = 50

both are working as a total.

****  but if i select LOCATION = EU or US, these formulae aren't working.

If i select location = US, it should display 

DIFFERENCE = 100-15 = 85


Creator II
Creator II

When you say the expressions aren't working - what are they doing?  Returning the wrong value?  Zero? Null?

What does "SUM({1<QTR={'2019Q1'},FLAG={‘Y’}>}DOLLAR)" by  itself return when you filter by a country?

Contributor III
Contributor III

it's returning NULL (displaying '-').

Creator II
Creator II

Did you load the Excel data as an island table ie not joined to the rest of the data model?

If it is joined, then even with the {1<>} in the set analysis, you can end up with undesirable results. In this case for example trying to associate a result per Country as a dimension where there is no country in the Excel data.