4 Replies Latest reply: Jul 1, 2017 8:15 AM by Manish Chauhan RSS

    Min/Max Bands

    Manish Chauhan

      I am trying to replicate some of the charting that we currently do in excel within QlikView/QlikSense without much luck and I was wondering whether anyone has come across a suitable solution.

       

      I have series date of commodities with weekly series date and a series values. The series data I have goes back some 20 years. The objective is to replicate the charts in the attached document.

       

      Objective: To replicate the excel based charting within QlikView/QlikSense (see attached)

       

      Dimension: Series Date (Week of Year)

      Measures:

      1. 1.       2017 Series Value
      2. 2.       2016 Series Value
      3. 3.       Min series value based upon the series data from 2012-2015 (T-7 to T-2)
      4. 4.       Max series value based upon the series data from 2012-2015 (T-7 to T-2)
      5. 5.       Avg series value based upon the series data from 2012-2015 (T-7 to T-2)

       

      Visualisation:

      • 2017 Series: Blue line
      • 2016 Series: Black line
      • Avg line: Dotted line
      • Shading between Min and Max measure lines
        • Re: Min/Max Bands
          Kaushik Solanki

          Hi,

           

          What you need is an expression for all 5 measures.

           

          So in your expression you will have to either hard code the year or make it dynamic using the functions.

          And remove the second dimension year from chart.

           

          This presentation is pretty doable.

           

          Regards,

          Kaushik Solanki

            • Re: Min/Max Bands
              Manish Chauhan

              Thanks Kaushik

               

              I've been able to create the five measures (2017, 2016 series, Min, Max and Avg Series)

              SUM({$<series_year={2017}>} series_value)

              SUM({$<series_year={2016}>} series_value)

              MIN({$<series_year={2012, 2013,2014,2015}>} series_value)

              MAX({$<series_year={2012, 2013,2014,2015}>} series_value)

              AVG({$<series_year={2012, 2013,2014,2015}>} series_value)


              I have attached an example of the visual for one series. But how can I achieve the visulaisation of the shading between the min and max that I can achieve in Excel?


              Regards

              Manish