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

Trying to calculate yr by yr variances

I am new to qlikview, creating some simple dashboards, I am trying to calculate yr by yr variances in a pivot style table, ex. =Sum({<Yearnum>} Sales) - Sum({<Yearnum-1>} Sales).  Breaking this down, Sum (Sales) is my base line. Sum({<Yearnum>} Sales) returns same values as Sum (Sales). Testing Yearnum-1 returns expected year.

The full expression returns empty field. Any thoughts?

3 Replies
effinty2112
Master
Master

Hi Norman,

If Year is your year field then try these expressions

Total for 2017: Sum({$<Year = {2017} >}

Total for 2016: Sum({$<Year = {2016} >}


Substitute your Yearnum field and corresponding values if required.


You can also try

Total for selected year: Sum({$<Year = {$(=max(Year))} >}

Total for previous year: Sum({$<Year = {$(=-1+max(Year))} >}


Again substitute Yearnum field and corresponding values if required.


Good luck


Andrew

Not applicable
Author

Goal Trying to calculate yr by yr variances

Thx for feedback on original question.

Made some adjustments based on feedback and some qlikview videos

Sum({$<Year = {"2010"} >}Sales) - Expression works as is, but only one year

Sum({$<Year = {$(=max(Year))} >}Sales) - Expression works as is, but only max year of data

Sum({$<Year = {$(=-1+max(Year))} >}Sales) - Expression works as is, but only previous of max year of data

Sum({$<Year = {$(=(Year)-1)} >}Sales) -  Expression DOES NOT work????

Sum({$<Year = {$(=-1+max(Year))} >}Sales) - This will populate equivalent of Sum(Sales) for previous year of full years 2012 -2009. Keeping in mind original ask is variance for each year so (2009 assume blank), (2010-2009), (2011-2010), (2012-2011)

Year20092009201020102011201120122012
CategoryNameSum (Sales)Sum({$<Year = {2011} >}Sales)Sum (Sales)Sum({$<Year = {2011} >}Sales)Sum (Sales)Sum({$<Year = {2011} >}Sales)Sum (Sales)Sum({$<Year = {2011} >}Sales)
Total87666.2940371204.71610642508.7716642508.7716683656.40340

I am still assuming I need something like this:

Variance Current Year - Previous Year

Sum({$<Year = {$(=(Year))} >}Sales) - Sum({$<Year = {$(=(Year)-1)} >}Sales)

Any other feedback?

writetoprithvi
Contributor II
Contributor II

I have been on the same footing and struggling to get answer to this problem. Ideally, it should figure out the context of the visualization and pull the numbers but for some strange reason, even if your year is, say for example 2018, instead of taking that as the maximum year, it still considers max date of the whole data set as max date. The problem seems to be that dimensions which are part of visualization are not considered filters. Best of luck. If you get the answer, please share. I am struggling as well.