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
