1 Reply Latest reply: Dec 29, 2012 12:03 PM by Vishal Shekhawat RSS

    Qlikview Expression for Waterfall Chart

    Niky Rathod

      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.