Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm new to qlik and trying to use the before function. I understand it only goes back to the first column. What if data exists in the qvd but has been filtered out? Can the funtcion be used to calculate variance still? The table below is filtered to FY16 only but data is from FY11 onwards. I want to have it evaluate the variance between Sep 2015 and Aug 2015 which span two fiscal years. Thanks!
This is the current function I'm using
Sum([Charge Amount]) - Before (Sum([Charge Amount]))
Month | Sep-2015 | Oct-2015 | Nov-2015 | ||||||
Region | Charges | Prior Month Variance | % Change | Charges | Prior Month Variance | % Change | Charges | Prior Month Variance | % Change |
A | $459,098.63 | - | - | $582,796.03 | $123,697.41 | 26.94% | $665,915.23 | $83,119.20 | 14.26% |
B | $31,387.26 | - | - | $46,741.91 | $15,354.65 | 48.92% | $41,737.80 | -$5,004.11 | -10.71% |
C | $29,671,346.89 | - | - | $29,636,065.18 | -$35,281.71 | -0.12% | $30,033,306.01 | $397,240.83 | 1.34% |
D | $27,893,210.43 | - | - | $28,455,651.75 | $562,441.32 | 2.02% | $27,240,995.76 | -$1,214,655.99 | -4.27% |
E | $16,730,844.40 | - | - | $16,794,022.56 | $63,178.15 | 0.38% | $16,757,075.63 | -$36,946.93 | -0.22% |
In this case before doesn't work. You have to use Set analysis.
Search in community for Set analysis and you will find lot of guides. If you have a trouble ping here.