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

31 Replies
jleefjcapital
Creator II
Creator II
Author

It doesn't like the Order by statement

The following error occurred:

Unknown statement

The error occurred here:

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

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

EARNINGS_MSA:

LOAD*,

    TOTPRIV_MSA_Delta/[TOTPRIV_MSA] as [TOTPRIV_MSA_%]; 

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

jleefjcapital
Creator II
Creator II
Author

Should the order by statement come before the From statement?

sunny_talwar

No it should be after FROM. Is it still giving error??

jleefjcapital
Creator II
Creator II
Author

Actually, I get an error that way as well.  It works if I remove it.

sunny_talwar

Really, not sure why it is doing that. Is the result what you expect? or its just running with without errors?

jleefjcapital
Creator II
Creator II
Author

Not really.  So I tried creating a new load statement -  pointing to the original statement.   But, now it doesn't like the resident statement.  I don't understand what I'm doing wrong, but is this the correct syntax? 

EARNINGS_MSA_DELTA:

LOAD*,

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

Resident EARNINGS_MSA;

Order by DATE;

EARNINGS_MSA_PERCENT: 

LOAD*,

   TOTPRIV_MSA_Delta/[TOTPRIV_MSA] as [TOTPRIV_MSA_%];

Resident EARNINGS_MSA_DELTA;

Order by DATE;

Here is a sample of the original load statements:

EARNINGS_MSA:

LOAD

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

    value as [TOTPRIV_MSA],   

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

CONCATENATE(EARNINGS_MSA)

LOAD

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

    value as [TOTPRIV_MSA],

    'Akron, OH' as [MSA],

    'Ohio' as [STATE]

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

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

CONCATENATE(EARNINGS_MSA)

LOAD

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

    value as [TOTPRIV_MSA],

    'Albany-Schenectady-Troy, NY' as [MSA],

    'New York' as [STATE]

FROM [lib://Average Hourly Earnings of All Employees Total Private in Albany-Schenectady-Troy, NY (MSA)]

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

CONCATENATE(EARNINGS_MSA)

LOAD

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

    value as [TOTPRIV_MSA],

    'Albany, GA' as [MSA],

    'Georgia' as [STATE]

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

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

sunny_talwar

Try this:

EARNINGS_MSA:

LOAD

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

    value as [TOTPRIV_MSA],  

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

CONCATENATE(EARNINGS_MSA)

LOAD

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

    value as [TOTPRIV_MSA],

    'Akron, OH' as [MSA],

    'Ohio' as [STATE]

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

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

CONCATENATE(EARNINGS_MSA)

LOAD

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

    value as [TOTPRIV_MSA],

    'Albany-Schenectady-Troy, NY' as [MSA],

    'New York' as [STATE]

FROM [lib://Average Hourly Earnings of All Employees Total Private in Albany-Schenectady-Troy, NY (MSA)]

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

CONCATENATE(EARNINGS_MSA)

LOAD

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

    value as [TOTPRIV_MSA],

    'Albany, GA' as [MSA],

    'Georgia' as [STATE]

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

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

EARNINGS_MSA_DELTA:

LOAD *,

          TOTPRIV_MSA_Delta/[TOTPRIV_MSA] as [TOTPRIV_MSA_%];

LOAD *,

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

Resident EARNINGS_MSA

Order by DATE;


DROP Table EARNINGS_MSA;

jleefjcapital
Creator II
Creator II
Author

Do i need to switch around the two statements?  Because it's being used prior to creation....or does it not matter? 

LOAD *,

          TOTPRIV_MSA_Delta/[TOTPRIV_MSA] as [TOTPRIV_MSA_%];

LOAD *,

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

sunny_talwar

It does matter because I am using the preceding load where the execution happens bottom up. Look at the following link for information on preceding load: Preceding Load

sunny_talwar

What I have is right order