topic Set Analysis: What am i Doing wrong? in QlikView

Set Analysis: What am i Doing wrong?

Wed, 25 Nov 2020 16:16:04 GMT

HI there

I'm trying to apply the Set statement, with the dollar sign expansion, to calculate the previous year's Value but i keep on getting the selected year's Value only. Note Year field is called EYear and is simply the year.

I've tried the following:

1. =Sum( {$<EYear = {$(=(EYear)-1)}>} Value)

2. =Sum( {$<EYear = {$(=Only(EYear)-1)}>} Value )

3. =Sum( {$<EYear = {"$(=Only(EYear)-1)"}>} Value )

It doesn't work in a bar chart and it doesn't work in a text box. What am i doing wrong?

Re: Set Analysis: What am i Doing wrong?

arulsettu — Wed, 30 Mar 2016 07:04:36 GMT

may be this

Sum( {$<EYear = {$(=Max(EYear)-1)}>} Value)

Re: Set Analysis: What am i Doing wrong?

ahmar811 — Wed, 30 Mar 2016 07:08:36 GMT

clinton reddy

try this expression

=Sum( {$<EYear = {"=$(=max(EYear)-1)"}Value )

hope this help you

Thanks & Regards

Ahmar Ansari

Re: Set Analysis: What am i Doing wrong?

Kushal_Chawda — Wed, 30 Mar 2016 07:10:32 GMT

try this

=Sum( {$<EYear = {"$(=max(EYear)-1)"}>} Value)

Re: Set Analysis: What am i Doing wrong?

Chanty4u — Wed, 30 Mar 2016 07:31:18 GMT

chk dis

sum({$Eyear={$(=Max(Eyear)-1)}>}value)

Re: Set Analysis: What am i Doing wrong?

Gysbert_Wassenaar — Wed, 30 Mar 2016 07:51:32 GMT

The second and third look like they should work. Please post a small qlikview document that demonstrates the problem.

Re: Set Analysis: What am i Doing wrong?

Wed, 30 Mar 2016 09:21:23 GMT

HI all

Thanks for the responses. See attached a Qlik document, Excel file and screenshot.

I've added fields (A) "Date" and "Sales" exactly equal to (B) "EYear" and "Value". The result is (A) partially works and (B) does not.... why?

When I use {1}, I can select anywhere and get the Sales Total for the selected Year - not the Total Sales across all years. Is this expected?

When I use {$} I have to make the "Year" selection to get the required result.

The Syntax and Results are exceptionally confusing. Imo, the responses confirm this, that is, there are little technical differences in each approach. I've spent 6+hours on this and with standard excel Pivot tables it takes literally seconds... so frustrating!

Re: Set Analysis: What am i Doing wrong?

sunny_talwar — Wed, 30 Mar 2016 09:28:03 GMT

Do you have a sample you can share? As Gysbert pointed out that the last two expression should give you previous year data for value

Re: Set Analysis: What am i Doing wrong?

Wed, 30 Mar 2016 09:30:41 GMT

HI Gystbert

I've tried all the responses. Whenever I use EYear though my charts do not change, i.e. current and previous year result is equal.

"Year" seems to be playing some role in the outputs. As soon as i change Year to "EYear" nothing works, i.e. i get the same values in each graph - not different.

Re: Set Analysis: What am i Doing wrong?

tresB — Wed, 30 Mar 2016 09:46:09 GMT

In fact, ALL three expressions should work fine when a single value is selected from EYear. If that is not working, most probably your EYear field values are not simple numeric like, 2012, 2013,... rather with some text representation like FY2012, FY2013... or so. If you could post a sample data set, the issue could be looked into and resolved.

Re: Set Analysis: What am i Doing wrong?

Anonymous — Wed, 30 Mar 2016 10:09:51 GMT

=Sum( {$<EYear = {"$(=max(EYear)-1)"}>} Value)

Re: Set Analysis: What am i Doing wrong?

Gysbert_Wassenaar — Wed, 30 Mar 2016 10:17:42 GMT

That's because EYear and Eyear are two different fields to Qlikview. Make sure to use the exact case sensitive file name.

Re: Set Analysis: What am i Doing wrong?

Wed, 30 Mar 2016 10:28:42 GMT

Hi Gysbert

You are correct. I checked the field names and i was using "EYear" and should have used "Eyear".

I'm so dumb and embarrassed. Sorry for wasting everyone's time. I appreciate all the quick responses!

kush14101987‌ arulsettu‌ ahmar811‌ chanty4u‌ @sunnyT tresesco‌ prashantsha‌

Re: Set Analysis: What am i Doing wrong?

Wed, 30 Mar 2016 10:34:59 GMT

Ps. I was wondering why the field name was accepted...

I added the field name to a text box: =EYear. It showed 'Bad field name ' whereas in the Set expression i created this was not indicated. Perhaps this helps someone oneday