QS Pivot Table Sorting with Set Expression

JohanLind1979 — Thu, 29 Apr 2021 09:15:18 GMT

Hello, I´m trying to implement Pivot Table Sorting with the help of this link https://www.youtube.com/watch?v=iiMlRVJWmsk

My problem is that I need to use the ' sign in my formula. Since the entire formula needs to be put into a variable as text with ' sign, then the SET expression and If statement don´t work. I tried to use chr(39) in the SET expression and if statement, which works perfectly in a text box, but not when I´m using the variable input buttons.

Original expression that I want to use for sorting:

-sum({<Version={'Actuals'}>} if(DisplayCurrency='SEK',Amount/FactorNr, if(DisplayCurrency='EUR',AmountEUR/FactorNr, if(DisplayCurrency='DKK',AmountDKK/FactorNr, if(DisplayCurrency='NOK',AmountNOK/FactorNr,'0')))))

I tried this for the variable input:

'sum({<Version={'&chr(39)&'Actuals'&chr(39)&'}>} if(DisplayCurrency='&chr(39)&'SEK'&chr(39)&',Amount/FactorNr, if(DisplayCurrency='&chr(39)&'EUR'&chr(39)&',AmountEUR/FactorNr, if(DisplayCurrency='&chr(39)&'DKK'&chr(39)&',AmountDKK/FactorNr, if(DisplayCurrency='&chr(39)&'NOK'&chr(39)&',AmountNOK/FactorNr,'&chr(39)&'0'&chr(39)&')))))'&'~one|2~two'

The first chr(39) seems to be interpreted as '. But as soon as I put in anything after the first chr(39), then the variable vPivotSort gets blank. For example 'sum({<Version={'&chr(39) works fine, but 'sum({<Version={'&chr(39)&'Actuals' gives me a blank variable.

What can I do to solve this problem?

topic QS Pivot Table Sorting with Set Expression in QlikView

QS Pivot Table Sorting with Set Expression