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

[flames]

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.

(SUM({1<NEWQTR={$(VLQTR)},VAR={‘FIXED’}>}NUMERATOR)/ SUM({1<NEWQTR={$(VLQTR)},VAR={‘FIXED’}>} DENOMINATOR)

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

[rogerpegler]

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:

LOAD
    ExpressionID, 
  Expression
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.

[flames]

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.

FLAG	QTR	DOLLAR
Y	2019Q1	100

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

[rogerpegler]

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?

[flames]

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

[rogerpegler]

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.