Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Min/Max Bands

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
4 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anonymous
Not applicable
Author

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

See this.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anonymous
Not applicable
Author

Thank you Kaushik

However this is purely area chart with two measures. What I want to achieve is a combination of area chart and line chart.

For measures 2017, 2016 series and Average I need a line chart.

For measures Min and Max I need an area chart.

Of course the 2017,2016 and Avg measures but be overlayed on top of the shading.

Is this possible in QlikView/Sense?

regards

Manish