Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
That's because EYear and Eyear are two different fields to Qlikview. Make sure to use the exact case sensitive file name.
may be this
Sum( {$<EYear = {$(=Max(EYear)-1)}>} Value)
hi
try this expression
=Sum( {$<EYear = {"=$(=max(EYear)-1)"}Value )
hope this help you
Thanks & Regards
Ahmar Ansari
try this
=Sum( {$<EYear = {"$(=max(EYear)-1)"}>} Value)
chk dis
sum({$Eyear={$(=Max(Eyear)-1)}>}value)
The second and third look like they should work. Please post a small qlikview document that demonstrates the problem.
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!
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
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.
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.