Pick(Match(Waterfall,'Previous FY','AB1','AB2','AB3','AB4','AB5','AB6','AB7','AB8','AB9','AB10','AB11'),
Sum({$<Flag={'1'},Year={$(=max(Year)-1)}>}Amount/1000000), //Previous FY
Sum({$<Flag={'2'}>} Amount/1000000), //AB1
Sum({$<Flag={'3'},OL_DateID={"<=$(=Max(OL_ReportDateID) – 1)"}>}Amount/1000000) + Sum({$<Flag={'4'},OL_DateID={">=$(=Max(OL_ReportDateID))"}>}Amount/1000000), //AB2
Sum({$<Flag={'5'},OL_DateID={">=$(=Max(OL_ReportDateID))"}>}Amount/1000000), //AB3
Sum({$<Flag={'6'},OL_DateID={">=$(=Max(OL_ReportDateID))"}>}Amount/1000000), //AB4
Sum({$<Flag={'7'},OL_DateID={">=$(=Max(OL_ReportDateID))"}>}Amount/1000000), //AB5
Sum({$<Flag={'8'},OL_DateID={">=$(=Max(OL_ReportDateID))"}>}Amount/1000000), //AB6
Sum({$<Flag={'9'},OL_DateID={">=$(=Max(OL_ReportDateID))"}>}Amount/1000000), //AB7
Sum({$<Flag={'10'},OL_DateID={">=$(=Max(OL_ReportDateID))"}>}Amount/1000000), //AB8
Sum({$<Flag={'11'},OL_DateID={">=$(=Max(OL_ReportDateID))"}>}Amount/1000000), //AB9
(RangeSum(Above(`Value`,1,8)) - RangeSum(Above(`Value`,9,1)))*-1, //AB10
RangeSum(Above(`Value`,2,8))) //AB11
I use this expression for Waterfall chart with Dimension as : =WaterFall
This data flow from SQL to a QvW app. Except for AB3 and AB4 all data is plotted accurately.
I match the data for the current month (Filter on Month) and via Set Analysis I control the data to match the current month to remaing months in the calendar/ financial year.
I have ensured in SQL that the values for AB3 and AB4 is correct and may have issue when flowing in QvW.
