Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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?
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.