Bar graph show previous year values based on selection

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Some Background:

I have quarterly sales data from year 2010 to 2018.I am trying to show the bar graph for this data where my bars represent sales for each year.

I want a user to select the financial year and/or quarter and then bar graph should show data till 3 years prior to selected date

For example:

Part A:

If a user selects financial year 2018 and no quarter then I want bar graph to display bars indicating sales for

2018

2017

2016

2015

Part B:

If a user selects financial year 2018 and quarter Q2 then bar graph should show :

2018 Q2

2018 Q1

2017 Q4

2017 Q3

2017 Q2

2017 Q1

2016 Q4

2016 Q3

2016 Q2

2016 Q1

2015 Q4

2015 Q3

2015 Q2

Depending upon whether or not quarter is selected the behavior of chart should be different and depending upon quarter the previous

3 years should be shown

The problem I am facing:

I am able to display the 3 previous year depending upon the current selection for financial year (Part A of example) when only financial year is selected.

But I am not able to achieve the Part B of example where a user selects a quarter along with financial year and then my chart should show bars 3 years prior to financial year selection at quarter level.

Also I have another expression in the Qlikview which calculates the cumulative sales I also want that to display correctly when a quarter is selected along with financial year.

The expression in chart

Sales:

sum({$<[Financial Year] = {">=$(=$(Selected_Year_3)) <=$(=$(Selected_Year))"}>}Sales)

Cumulative Sales:

RangeSum(Above(sum({<[Financial Year] = {">=$(=$(Selected_Year_3)) <=$(=$(Selected_Year))"}>}Sales), 0, rowno()))

Variables:

Selected_Year = GetFieldSelections([Financial Year])

Selected_Year_3 =GetFieldSelections([Financial Year]) - 3

Dimension:

Financial_Year

Attached is the data and qlikview file.Any help will be appreciated

Please ask if you need any clarification or if anything is difficult to understand

Re: Bar graph show previous year values based on selection

Anonymous — Wed, 05 Sep 2018 19:03:56 GMT

Attaching the updated qvw file.Please consider this one

Re: Bar graph show previous year values based on selection

neelamsaroha157 — Wed, 05 Sep 2018 19:12:23 GMT

Do you have a date-field in your real application?

Re: Bar graph show previous year values based on selection

Anonymous — Wed, 05 Sep 2018 19:17:13 GMT

My data is same as attached in excel

I have columns like Financial Year, Quarter

Re: Bar graph show previous year values based on selection

neelamsaroha157 — Wed, 05 Sep 2018 19:29:53 GMT

Check this out- You can also use single expression with if condition.

Re: Bar graph show previous year values based on selection

Anonymous — Wed, 05 Sep 2018 19:45:56 GMT

Just amazed by the speed with which you did it.

Helpful to everyone as usual

Thank You so much

topic Re: Bar graph show previous year values based on selection in QlikView

Bar graph show previous year values based on selection

Re: Bar graph show previous year values based on selection

Re: Bar graph show previous year values based on selection

Re: Bar graph show previous year values based on selection

Re: Bar graph show previous year values based on selection

Re: Bar graph show previous year values based on selection