topic Re: No results with set analysis in QlikView

No results with set analysis

curiousfellow — Fri, 19 May 2017 14:15:59 GMT

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

Re: No results with set analysis

sunny_talwar — Fri, 19 May 2017 14:21:09 GMT

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)

Re: No results with set analysis

avinashelite — Fri, 19 May 2017 14:33:20 GMT

try like this

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

Re: No results with set analysis

Anonymous — Fri, 19 May 2017 14:34:40 GMT

or this

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

Re: No results with set analysis

curiousfellow — Fri, 19 May 2017 16:14:56 GMT

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.

Re: No results with set analysis

Anonymous — Fri, 19 May 2017 17:59:01 GMT

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)

Re: No results with set analysis

curiousfellow — Fri, 19 May 2017 18:36:46 GMT

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

Re: No results with set analysis

Anil_Babu_Samineni — Fri, 19 May 2017 18:39:29 GMT

Can you tell us, How you define the variable?

Re: No results with set analysis

curiousfellow — Fri, 19 May 2017 18:58:37 GMT

=only(addmonths(getfieldselections(date),-1))

Re: No results with set analysis

Anonymous — Fri, 19 May 2017 19:02:33 GMT

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

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

Re: No results with set analysis

Anil_Babu_Samineni — Fri, 19 May 2017 19:04:42 GMT

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

Re: No results with set analysis

Digvijay_Singh — Fri, 19 May 2017 19:08:02 GMT

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?

Re: No results with set analysis

curiousfellow — Fri, 19 May 2017 19:17:17 GMT

Only is one of the many scenarios I tried. I tried without too. Textbox gives correct result.

Re: No results with set analysis

sunny_talwar — Fri, 19 May 2017 19:18:38 GMT

I suggest looking hereDates in Set Analysis‌ and/or providing a sample so that we can see the issue

Re: No results with set analysis

Anonymous — Fri, 19 May 2017 19:22:16 GMT

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.

Re: No results with set analysis

curiousfellow — Fri, 19 May 2017 19:25:18 GMT

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

Re: No results with set analysis

sunny_talwar — Fri, 19 May 2017 19:25:26 GMT

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)

Re: No results with set analysis

curiousfellow — Fri, 19 May 2017 19:33:11 GMT

Is says sum({date=1-5-2016}>}amount)

Re: No results with set analysis

Digvijay_Singh — Fri, 19 May 2017 19:38:05 GMT

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?

Re: No results with set analysis

curiousfellow — Fri, 19 May 2017 19:47:32 GMT

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