Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Year on Year Change

Hi,

I have been searching the forums and my question has been asked multiple times but I can't get any of the answers to work even though my data looks identical. I am trying to calculate the change between the 2013 and 2018 data for each Category and Area name. So for Europeans living in Linden, it is 1932 - 1842 = 90.  I have tried using variations of 

sum({$<Year = {2018}>}[SA2-ethnic_group.Subtotal]) - sum({$<Year = {2013}>}[SA2-ethnic_group.Subtotal]) but that  I think will only work if my Years are in separate columns. The answer I get is 1932 for 2018 as it has no value for 2013. I tried "above" but the set expression seems invalid. Without the set expression, it just subtracts the figure above it and as my offset will vary, I cant specify an offset. Also tried creating a pivot table with Category as a column and Years/Subtotal as separate columns but it gave me the same answer.  

Any help would be appreciated. Thanks, David

Census Data.jpg

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Year on Year Change

It will become easier to accomplish if you don't keep year as a dimension in your table.

image.png

Diff: sum({$<Year = {2018}>}[Subtotal]) - sum({$<Year = {2013}>}[Subtotal]) 

See attached qvf.

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

5 Replies
Highlighted
Partner
Partner

Re: Year on Year Change

It will become easier to accomplish if you don't keep year as a dimension in your table.

image.png

Diff: sum({$<Year = {2018}>}[Subtotal]) - sum({$<Year = {2013}>}[Subtotal]) 

See attached qvf.

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Highlighted
Partner
Partner

Re: Year on Year Change

PS

A tip for the future. It is easier to help with an example if you provide a sample app or sample data that is possible to reuse by the community. It is time consuming to recreate a data sample from a image.

DS

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Contributor III
Contributor III

Re: Year on Year Change

Thanks, will provide data the next time I post. I was heading down that soln but was hoping to keep the year as a dimension but just looked at the soln a little closer and it is exactly what I wanted. Not sure why I didn't think of removing my year as a dimension

Highlighted
Partner
Partner

Re: Year on Year Change

If it was possible - how would you expect the data from my example qvf to be presented?

image.png

Which values for which Year-Name-Category-row?

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Contributor III
Contributor III

Re: Year on Year Change

I am happy with your previous soln, just created another table looking like the soln you posted by deleting the Year Dim. What I was originally trying to show is in your latest post with the difference column and the 2018 rows would have the difference between 2018 and 2013. The 2013  rows will be blank. The first soln actually makes more sense