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
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;
% 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?
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.
Do you have a sample you can share?
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
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
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]);
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
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');
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');