Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Gysbert_Wassenaar

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
arulsettu
Master III
Master III

may be this

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

ahmar811
Creator III
Creator III

hi

clinton reddy

try this expression

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


hope this help you

Thanks & Regards

Ahmar Ansari



Kushal_Chawda

try this

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

Chanty4u
MVP
MVP

chk dis

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

Gysbert_Wassenaar

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
Not applicable
Author

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!

sunny_talwar

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

Not applicable
Author

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.

tresesco
MVP
MVP

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.