26 Replies Latest reply: May 20, 2017 8:57 AM by Stefan Wühl

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

Do you have a single selection made in date field? Also, do you get the same format as your date field for the variable named "variable"? If the answer to the above two question is yes, then try this

=Sum({<date={"\$(=variable)"}>} amount)

or this

=Sum({<date={"\$(=\$(variable))"}>} amount)

try like this

sum({<date={'\$(variable)'}>} amount)

or this

=Sum({<date={"\$(=Date(\$(variable)))"}>} amount)

It still doesn't work. Perhaps it's better I first start my weekend and try after the weekend again with a clear mind.

Thank you all for your quick respons.

What does the value of the variable show?  And what does your Date field look like?

As a shot in the dark you could try:

sum({<date={'(variable)'}>} amount)

The variable has the same format as the datefield itself. For instance 1-5-2017

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?

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?

Can you tell us, How you define the variable?

You need to have it ignore the date selection and just use variable.  Like:

=sum({<date={'\$(variable)'}, date=>} amount)

Correct me if I am wrong. But doesn't set analysis overrules selections made ?

I guess I learn something everyday.

Why you need Only() here? Create variable like below in overview of variable

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

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.

Check this out

Script

Table:

LOAD Date(MonthStart(Date#(date, 'YYYY-MM')), 'YYYY-MM') as date,

amount;

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)

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

Try this

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

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)

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.

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

Still a lot to learn.

Thanks to all who tried to help me.

Next challenge is to do the same with count distinct numbers

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))

Have a look at

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