No results with set analysis

[curiousfellow]

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

[sunny_talwar]

Try this

Above(Sum({<date = {'$(=vVar)'}>}amount))

[Anonymous]

I guess I learn something everyday.

[swuehl · Accepted solution]

It's crucial to add context to any issue description.

As far as I understood, and what your original post is missing, you are trying to use your expression in a chart with dimension date and you expect the value for the previous month to appear on the line with the current month date.

Obviously, that's out of the aggregation scope.

You can circumvent this by adding the TOTAL qualifier to your sum aggregation:

Sum( TOTAL {YourSet}>} date)

[Peter_Cammaert]

What Stefan is saying is that you may have created a correct set of data before calculation of the table object starts, but your row-by-row dimension values will always be applied as a last step before calculating the expression outcome for a particular row. If your dimension columns include a calendar value, there will be no escaping that reduction using set analysis alone. The TOTAL keyword offers you a way to circumvent that limitation somehow.

[curiousfellow]

I am sorry for not being totally clear. I did not know that the dimensions affect the result of a set analysis expression.

Stefan's answer and Peter's additional explanation makes that I learned somthing more about how Qlik works.

Still a lot to learn.

Thanks to all who tried to help me.

Next challenge is to do the same with count distinct numbers

[sunny_talwar]

Just wondering if date was the dimension? If it was, this did not work using Above or Above(TOTAL...)?

Above(Sum({<date = {'$(=vVar)'}>}amount))

or

Above(TOTAL Sum({<date = {'$(=vVar)'}>}amount))

[swuehl]

Have a look at

The Aggregation Scope

to learn more about the aggregation scope.

I recommend also to read also all other blog posts and technical briefs of Henric. IMHO, they answer more than 90% of the questions here in the forum.

And to make it clear, you are of course free to ask any question you like.

All I want to ask for is that the question is as detailed as possible, always containing

a) A description of your current setting (data model, chart used, dimensions used, expressions used), any selections active or any other changes to the default set up (e.g. alternate states, triggers).

b) a description of what you want to achieve, a written requirement or a sample chart

c) What you already tried to solve your requirement and both what you got returned and what you expected to see instead.

It's most simple to post a small sample QVW, so a) and parts of c) can be seen by looking at the application. And it's easy to test any suggested solution.

Regards,

Stefan