Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am using Qlik Sense - June 2018 Version.
I am trying to retrieve the values from a set period of time (in this case quarters). I am able to do this using this formula:
sum( {$<MonthNum = {'>0<4'}>} [QTY Shipped LB] )
which works quite well. However I want to get creative and have been using variables on a straight table so that they mimic the "alternative measures" found in graphing. I am able to pick a unit of measure (Lbs, $, Margin) from a variable extension I have simply by clicking on a button.
My problem is I have a variable called "vMeasure", but no matter how I either change the Set Analysis or try and use it in other formulas nothing appears to work.
Any help you can give would be appreciated. Or a point in the right direction. I have searched all over but could never find what I need.
If vMeasure is Sum(SALES), then that means that you are trying to nest a sum expression inside another. Nested aggregations are not allowed, so this expression is failing.
In essence, you would need something like
sum({$<MonthNum = {">0<4"}>} Aggr($(vMeasure), dim1, dim2))
(where dim1, dim2,...dimN would be the chart dimensions)
But perhaps you should explain why you would want to nest the sums. What you are asking for seems to be a complicated and expensive way of saying
Sum({$<MonthNum = {">0<4"}>} SALES)
Have you tried setting vMeasure to "SALES"?
Hi Michael,
it's not clear what problem exactly are you facing, but I'll post a working example, maybe it helps you undrestant things better.
Sum({<$(sIgnoreDateFields), Year={$(sMaxYear)}, %IDMonth={"<=$(sMaxMonthID)"}>}[$(vMeasure)]/if(vMeasure='#Sales',$(vCurrencyRate),1))
Where
sIgnoreDateFields = Year,Quarter,Month,[Year-Month] //to turn off selected time filters
sMaxYear = Max(Year) //to select particular year
sMaxMonthID = Max(%MonthID) //to select particular month(s)
vMeasure is set by a button (#Sales or #Quantity)
vCurrencyRate is set by a button to a selected currency rate.
This expression calculates YTD sum of Sales or Quantity (based on user input via button) for a particular year. If user chooses Sales, it also devides the sum by a user chosen exchange rate .
Hope this helps.
Juraj
PS: your example contains a typo. If you want to make a search in set analysis, you should always use double quotes:
sum( {$<MonthNum = {">0<4"}>} [QTY Shipped LB] )
Thanks for the response Juraj. I am trying to use a formula similar to this one but use my variable.
sum( {$<MonthNum = {">0<4"}>} [QTY Shipped LB] ) original formula
sum( {$<MonthNum = {">0<4"}>} $(vMeasure) ) formula I wish I could use
When I try the formula with a variable in it I get all null values. I will try to digest what you wrote. I am fairly new to this.
How is vMeasure defined?
make sure it doesnt have an =
How is vMeasure defined?
vMeasure is defined as :
Sum(SALES)
There is no "=".
I then use an Extension that gives me the ability to pick alternative measures as well.
And you want to use it as
sum( {$} $(vMeasure) )
?
If so vMeasure should just be the column
Thank You
Dilip Ranjith
Sent via mobile
Dilip - anytime I use a variable with a sum(or other aggregate) it never works. See picture of report.
If vMeasure is Sum(SALES), then that means that you are trying to nest a sum expression inside another. Nested aggregations are not allowed, so this expression is failing.
In essence, you would need something like
sum({$<MonthNum = {">0<4"}>} Aggr($(vMeasure), dim1, dim2))
(where dim1, dim2,...dimN would be the chart dimensions)
But perhaps you should explain why you would want to nest the sums. What you are asking for seems to be a complicated and expensive way of saying
Sum({$<MonthNum = {">0<4"}>} SALES)
Have you tried setting vMeasure to "SALES"?
Jonathan - your formula works perfectly. The reason I needed it is because I have buttons on the top of the report that determine which unit of measurement the report is shown in. I do not want it to show always in SALES (dollars), I want the user to determine which type of information he sees.
What you have shown me is exactly what I have been looking for.
Thanks.