Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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');
Should the order by statement come before the From statement?
No it should be after FROM. Is it still giving error??
Actually, I get an error that way as well. It works if I remove it.
Really, not sure why it is doing that. Is the result what you expect? or its just running with without errors?
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]);
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;
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
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
What I have is right order