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

Bar graph show previous year values based on selection

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





1 Solution

Accepted Solutions
neelamsaroha157
Specialist II
Specialist II

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

View solution in original post

5 Replies
Anonymous
Not applicable
Author

Attaching the updated qvw file.Please consider this one

neelamsaroha157
Specialist II
Specialist II

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

Anonymous
Not applicable
Author

My data is same as  attached in excel

I have columns like Financial Year, Quarter

neelamsaroha157
Specialist II
Specialist II

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

Anonymous
Not applicable
Author

Just amazed by the speed with which you did it.

Helpful to everyone as usual

Thank You so much