Min/Max Bands

[Anonymous]

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

[kaushiknsolanki]

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

[Anonymous]

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]

Hi,

See this.

Regards,

Kaushik Solanki

[Anonymous]

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