topic Trying to calculate yr by yr variances in QlikView

Trying to calculate yr by yr variances

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Trying to calculate yr by yr variances

effinty2112 — Sat, 23 Sep 2017 09:13:28 GMT

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

Re: Trying to calculate yr by yr variances

Sat, 23 Sep 2017 14:58:02 GMT

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)

Year	2009	2009	2010	2010	2011	2011	2012	2012
CategoryName	Sum (Sales)	Sum({$<Year = {2011} >}Sales)	Sum (Sales)	Sum({$<Year = {2011} >}Sales)	Sum (Sales)	Sum({$<Year = {2011} >}Sales)	Sum (Sales)	Sum({$<Year = {2011} >}Sales)
Total	87666.294	0	371204.7161	0	642508.7716	642508.7716	683656.4034	0

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?

Re: Trying to calculate yr by yr variances

writetoprithvi — Sat, 26 Sep 2020 06:06:49 GMT

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.