2 Replies Latest reply: Aug 7, 2018 10:17 AM by Ken Harry RSS

    Editing a Cumulative graph to remove variables

    Ken Harry

      Hello Qlik friends, its me again!!!

       

      I am trying to edit a formula to remove variables and set the graph to only reflect certain field. The way the graph was designed (not by me) was to select filters, and the graph would change. I am trying to make Finance group = Savings and Fiscal year  = 2018. Here is the formula:

      (

      if(GetFieldSelections(CalcType) = 'Cumulative',


      rangesum(

      above(Sum({<Baseline=,

      Baseline_LE=,

      [Snapshot Type] = {'LE'},

      [LE Breakup]={'ACTUAL'},[SC/Non-SC]={"$(=$(vSCNonSC))"},

      [SC Finance Filter]={1}

      ,[Fiscal Year] = {$(=$(vFiscalYear))}

      ,[Finance Group] = {"$(=$(vFG))"}

      //,Baseline={$(=$(vBaseline))}

      //,[Direct Indirect Flag]={$(=$(vDirectIndirect))}

      //

      //,[PCI Type] = {$(=$(vPCIType_Combo))}

      ,[Default Type] = {$(=$(vCFType))}

      //,[Direct Indirect Flag]={$(=$(vDirectIndirect))}

      //--

      >}

      $(vFinanceGroupAmount)),0,RowNo()))



      ,Sum({<Baseline=,

      Baseline_LE=,

      [Snapshot Type] = {'LE'},

      [LE Breakup]={'ACTUAL'},[SC/Non-SC]={"$(=$(vSCNonSC))"},

      [SC Finance Filter]={1}

      ,[Fiscal Year] = {$(=$(vFiscalYear))}

      ,[Finance Group] = {"$(=$(vFG))"}

      //,Baseline={$(=$(vBaseline))}

      //,[Direct Indirect Flag]={$(=$(vDirectIndirect))}

      //

      //,[PCI Type] = {$(=$(vPCIType_Combo))}

      ,[Default Type] = {$(=$(vCFType))}

      //,[Direct Indirect Flag]={$(=$(vDirectIndirect))}

      //--

      >}

      $(vFinanceGroupAmount))

      )

      )

      *

      $(vCurrencyConversion)




      /*

      (If(Only(CalcType) = 'Cumulative',

      rangesum( above( Sum({<Baseline=,Baseline_LE=,

      [LE Breakup]={'ACTUAL'}, [Project SC Filter]={1},

      [Snapshot Type] = {'LE'},

      [SC Finance Filter]={1}

      ,[PCI Type] = {'PROJECT'}

      ,[Direct Indirect Flag]={$(=$(vDirectIndirect))}>}

      $(vFinanceGroupAmount)),0,rowno())),


      Sum({<Baseline=, Baseline_LE=, [Project SC Filter]={1},

      [LE Breakup]={'ACTUAL'}, [Snapshot Type] = {'LE'}, [SC Finance Filter]={1},[PCI Type] = {'PROJECT'}

      ,[Direct Indirect Flag]={$(=$(vDirectIndirect))}>} $(vFinanceGroupAmount))

      )

      )

      *

      $(vCurrencyConversion)

      */



      Thank you for the help! One thing I tried was to just set "Finance group" = 'Savings' but that did not work for me.


      Ken

        • Re: Editing a Cumulative graph to remove variables
          Sunny Talwar

          May be try this

           

          (

          if(GetFieldSelections(CalcType) = 'Cumulative',


          rangesum(

          above(Sum({<Baseline=,

          Baseline_LE=,

          [Snapshot Type] = {'LE'},

          [LE Breakup]={'ACTUAL'},[SC/Non-SC]={"$(=$(vSCNonSC))"},

          [SC Finance Filter]={1}

          ,[Fiscal Year] = {2018}

          ,[Finance Group] = {"Savings"}

          //,Baseline={$(=$(vBaseline))}

          //,[Direct Indirect Flag]={$(=$(vDirectIndirect))}

          //

          //,[PCI Type] = {$(=$(vPCIType_Combo))}

          ,[Default Type] = {$(=$(vCFType))}

          //,[Direct Indirect Flag]={$(=$(vDirectIndirect))}

          //--

          >}

          $(vFinanceGroupAmount)),0,RowNo()))



          ,Sum({<Baseline=,

          Baseline_LE=,

          [Snapshot Type] = {'LE'},

          [LE Breakup]={'ACTUAL'},[SC/Non-SC]={"$(=$(vSCNonSC))"},

          [SC Finance Filter]={1}

          ,[Fiscal Year] = {2018}

          ,[Finance Group] = {"Savings"}

          //,Baseline={$(=$(vBaseline))}

          //,[Direct Indirect Flag]={$(=$(vDirectIndirect))}

          //

          //,[PCI Type] = {$(=$(vPCIType_Combo))}

          ,[Default Type] = {$(=$(vCFType))}

          //,[Direct Indirect Flag]={$(=$(vDirectIndirect))}

          //--

          >}

          $(vFinanceGroupAmount))

          )

          )

          *

          $(vCurrencyConversion)

          • Re: Editing a Cumulative graph to remove variables
            Ken Harry

            Worked perfectly, just needed some syntax help. thank you as always Sunny. Hope to not bother you for a few days.