Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
tuftonmc
Contributor III
Contributor III

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
Luminary Alumni
Luminary Alumni

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
tuftonmc
Contributor III
Contributor III
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
Luminary Alumni
Luminary Alumni

Hi,

See this.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
tuftonmc
Contributor III
Contributor III
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