Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I use set analysis a lot and get (mostly) the results I expect.
I think next expression is rather simple but it dont gives any result.
In a table I have a date and an amount.
Now I want to compare total amount with the total amount of previous month.
Date is in a listbox.
I have a variable : only(addmonths(getfieldselections(date),-1))
The variable has the correct value when I show it in a textbox
To calculate total sum of previous month I use the expression
=sum({<date={$(variable)}>} amount)
But result = -
"
Must be a very simple expression I suppose, but just don't see what I am doing wrong
Why you need Only() here? Create variable like below in overview of variable
=AddMonths(GetFieldSelections(date), -1)
And then check that variable in Text Object? =VaribleName
Then look whether it's working or not then we can talk about set analysis? Or provide application to look
Just to check what is getting calculated in set expression, In a straight table keep header of the expression column blank so that formula itself is a label. Now let us know what value do you see after date=, inside curly bracket?
Only is one of the many scenarios I tried. I tried without too. Textbox gives correct result.
I suggest looking hereDates in Set Analysis and/or providing a sample so that we can see the issue
Your syntax may or may not be right but you'll never get a value for previous month if you don't add the Date= to your set analysis.
When someone selects a date from a filter, Qlikview is limiting the data to just that month. When you're using set analysis it will operate against ONLY that filtered subset of data. So data is filtered to current month and set analysis is looking for prior month. You'll never get a number unless you have the set analysis ignore the filter selection and only look at the variable.
Correct me if I am wrong. But doesn't set analysis overrules selections made ?
Check this out
Script
Table:
LOAD Date(MonthStart(Date#(date, 'YYYY-MM')), 'YYYY-MM') as date,
amount;
LOAD * Inline [
date, amount
2017-05, 100
2017-04, 340
2017-03, 260
];
Main thing to note here is that I used a format of YYYY-MM for my date field and this format is needed to be used in set analysis.
My variable name is vVar
=Date(MonthStart(Max(date), -1), 'YYYY-MM')
And my expression is
=Sum({<date = {'$(=vVar)'}>}amount)
Is says sum({date=1-5-2016}>}amount)
I assume you forgot the initial curly bracket before the date, I think it should have single quote around your date. Can you share complete expression you are using now after applying suggestions from others?
When I use this expression. I get an extra row, where the value in the dimension date column equals the var value.
I am gonna quit now, my boss has to pay me overtime
Nice weekend to you all