Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Set Analysis: What am i Doing wrong?

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?

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Set Analysis: What am i Doing wrong?

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


talk is cheap, supply exceeds demand

View solution in original post

13 Replies
Highlighted
Master III
Master III

Re: Set Analysis: What am i Doing wrong?

may be this

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

Highlighted
Creator III
Creator III

Re: Set Analysis: What am i Doing wrong?

hi

clinton reddy

try this expression

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


hope this help you

Thanks & Regards

Ahmar Ansari



Highlighted

Re: Set Analysis: What am i Doing wrong?

try this

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

Highlighted
Champion III
Champion III

Re: Set Analysis: What am i Doing wrong?

chk dis

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Set Analysis: What am i Doing wrong?

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


talk is cheap, supply exceeds demand
Highlighted
Not applicable

Re: Set Analysis: What am i Doing wrong?

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!

Highlighted

Re: Set Analysis: What am i Doing wrong?

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

Highlighted
Not applicable

Re: Set Analysis: What am i Doing wrong?

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.

Highlighted
MVP
MVP

Re: Set Analysis: What am i Doing wrong?

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.