Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jleefjcapital
Contributor

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

Re: embedding a measure (constructed variable) in another measure

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;

31 Replies

Re: embedding a measure (constructed variable) in another measure

% 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
Contributor

Re: embedding a measure (constructed variable) in another measure

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.

Re: embedding a measure (constructed variable) in another measure

Do you have a sample you can share?

jleefjcapital
Contributor

Re: embedding a measure (constructed variable) in another measure

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

Re: embedding a measure (constructed variable) in another measure

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
Contributor

Re: embedding a measure (constructed variable) in another measure

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]);

Re: embedding a measure (constructed variable) in another measure

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
Contributor

Re: embedding a measure (constructed variable) in another measure

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');

Re: embedding a measure (constructed variable) in another measure

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');

Community Browser