Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jleefjcapital
Creator II
Creator II

embedding a measure (constructed variable) in another measure

Is it possible to reference a measure to construct another measure? 

I created a measure to calculate the % change over time.   Then, I would like to reference this new variable to create another measure needed for the range slider - 

i.e.,

% change measure -  

num(sum(RETAILTRADE_MSA)/below(sum(RETAILTRADE_MSA)) -1,'#.0%')

new measure for range slider -  this is required for the range slider extension that I am using.   It references the min/max values of the variable.   However, I would like to reference the % change measure.  

Sum({$<RETAILTRADE_MSA ={">=$(=vMin_RETAILJOB) <=$(=vMax_RETAILJOB)"}>} RETAILTRADE_MSA)

It is not working well, but not sure if it's the extension that is not working or if it's not working because it is not possible to reference another created measure. 

Thank you,

Jessica

1 Solution

Accepted Solutions
sunny_talwar

The above should be fine, but then why not this:

EARNINGS_MSA_DELTA:

LOAD *,

         ([TOTPRIV_MSA] - Alt(Peek('TOTPRIV_MSA'), [TOTPRIV_MSA]))/[TOTPRIV_MSA] as [TOTPRIV_MSA_%],

         [TOTPRIV_MSA] - Alt(Peek('TOTPRIV_MSA'), [TOTPRIV_MSA]) as TOTPRIV_MSA_Delta

Resident EARNINGS_MSA

Order by DATE;

DROP Table EARNINGS_MSA;

View solution in original post

31 Replies
sunny_talwar

% change measure -

num(sum(RETAILTRADE_MSA)/below(sum(RETAILTRADE_MSA)) -1,'#.0%')

Jessica, I am not entirely sure what you are trying to say. Is % change measure an expression label or a variable? and where are you wanting to refer it?

jleefjcapital
Creator II
Creator II
Author

The % change is another variable.  I've constructed a measure to compute the % change of the original variable that I've loaded.

Then I would like to reference the %change variable that I've created in a new measure to assign the min / max, which is necessary for the range slider extension.  I downloaded the range slider from Qlik Branch.

sunny_talwar

Do you have a sample you can share?

jleefjcapital
Creator II
Creator II
Author

This is the %change measure I've scripted - named 'Retail Trade Employees (%)'

num(avg(RETAILTRADE_MSA)/If(avg(RETAILTRADE_MSA) > 0, below(avg({<Year=>} RETAILTRADE_MSA))) -1,'#.0%')

This is the measure I need to use with the Range Slider - so I would like to know if I can use the newly created %change variable instead of the original variable here.  

avg({$<[Retail Trade Employees (%)] ={">=$(=vMin_RETAILJOBS) <=$(=vMax_RETAILJOBS)"}>} [Retail Trade Employees (%)])

This is the link to the Range Slider extension - Range Slider

sunny_talwar

I have not used the extension before so don't really know how it would work. But

num(avg(RETAILTRADE_MSA)/If(avg(RETAILTRADE_MSA) > 0, below(avg({<Year=>} RETAILTRADE_MSA))) -1,'#.0%')

this cannot be created in script, seems like a front end expression that is being used in a chart? If it is then I don't think you would be able to use it the way you are trying to use here:

avg({$<[Retail Trade Employees (%)] ={">=$(=vMin_RETAILJOBS) <=$(=vMax_RETAILJOBS)"}>} [Retail Trade Employees (%)])


But there might be a possibility to have an alternate solution.


HTH


Best,

Sunny

jleefjcapital
Creator II
Creator II
Author

So I would need to create the delta or %change variable in the load statement.   I tried this, but it didn't work as expected.

LOAD

    Date(Date#("date",'YYYY-MM-DD'),'M/D/YYYY') AS DATE,

    value as [TOTPRIV_MSA],

    value - Alt(Peek('value'), value) as TOTPRIV_MSA_Delta,

    value/below(value) as percentchange,

    'Abilene, TX' as [MSA],

    'Texas' as [STATE]

FROM [lib://Average Hourly Earnings of All Employees Total Private in Abilene, TX (MSA)]

(XmlSimple, table is [observations/observation]);

sunny_talwar

You need to have an order by statement also here and then calculate percentage change in preceding load:

LOAD *,

          TOTPRIV_MSA_Delta/[TOTPRIV_MSA] as percentchange,

LOAD

    Date(Date#("date",'YYYY-MM-DD'),'M/D/YYYY') AS DATE,

    value as [TOTPRIV_MSA],

    value - Alt(Peek('value'), value) as TOTPRIV_MSA_Delta,

    'Abilene, TX' as [MSA],

    'Texas' as [STATE]

FROM [lib://Average Hourly Earnings of All Employees Total Private in Abilene, TX (MSA)]

(XmlSimple, table is [observations/observation])

Order By Date(Date#("date",'YYYY-MM-DD'),'M/D/YYYY');

HTH

Best,

Sunny

jleefjcapital
Creator II
Creator II
Author

Sunny,


When I tried that, I get this error message

The following error occurred:

Syntax error, missing/misplaced FROM: EARNINGS_MSA: LOAD*, TOTPRIV_MSA_Delta/[TOTPRIV_MSA] as percentchange, LOAD Date(Date#("date",'YYYY-MM-DD'),'M/D/YYYY') AS DATE, value as [TOTPRIV_MSA], value - Alt(Peek('value'), value) as TOTPRIV_MSA_Delta, 'Abilene, TX' as [MSA], 'Texas' as [STATE] Order by DATE

The error occurred here:

EARNINGS_MSA: LOAD*, TOTPRIV_MSA_Delta/[TOTPRIV_MSA] as percentchange, LOAD Date(Date#("date",'YYYY-MM-DD'),'M/D/YYYY') AS DATE, value as [TOTPRIV_MSA], value - Alt(Peek('value'), value) as TOTPRIV_MSA_Delta, 'Abilene, TX' as [MSA], 'Texas' as [STATE] Order by DATE

Data has not been loaded. Please correct the error and try loading again.

Here's the load statement :

LOAD*,

    TOTPRIV_MSA_Delta/[TOTPRIV_MSA] as percentchange, 

LOAD

    Date(Date#("date",'YYYY-MM-DD'),'M/D/YYYY') AS DATE,

    value as [TOTPRIV_MSA],

    value - Alt(Peek('value'), value) as TOTPRIV_MSA_Delta,

    'Abilene, TX' as [MSA],

    'Texas' as [STATE]

    Order by DATE;

FROM [lib://Average Hourly Earnings of All Employees Total Private in Abilene, TX (MSA)]

(XmlSimple, table is [observations/observation]);

Order By Date(Date#("date",'YYYY-MM-DD'),'M/D/YYYY');

sunny_talwar

Sorry try this:

LOAD *,

        TOTPRIV_MSA_Delta/[TOTPRIV_MSA] as percentchange;

LOAD

    Date(Date#("date",'YYYY-MM-DD'),'M/D/YYYY') AS DATE,

    value as [TOTPRIV_MSA],

    value - Alt(Peek('value'), value) as TOTPRIV_MSA_Delta,

    'Abilene, TX' as [MSA],

    'Texas' as [STATE]

FROM [lib://Average Hourly Earnings of All Employees Total Private in Abilene, TX (MSA)]

(XmlSimple, table is [observations/observation])

Order By Date(Date#("date",'YYYY-MM-DD'),'M/D/YYYY');