Bar chart with year in x axis and difference of year and prev_year as measure

[amaaiia]

HI!

I want a bar chart with YEAR dimension in x axis, and that year sales measure - prev_year sales measure as measure. Imagine I have a filter panel with A alternate state where I have selected 2019 to 2022 years, so in this case I want a bar chart with 4 bars (one per year between 2019-2022), and each bar has the measure:

• 2022--> sum(sales of 2022)-sum(sales of 2021)
• 2021--> sum(sales of 2021)-sum(sales of 2020)
• 2020--> sum(sales of 2020)-sum(sales of 2019)
• 2019--> sum(sales of 2019)-sum(sales of 2018) --> 2018 is not selected in filter panel (alternate state A), but if i have data of this year in all of my loaded data, I have to take it.

I thought this could be possible with a measure: sum({A}sales)-sum({A <year={$(=max(year)-1)}>}sales) for each bar in the bar chart, but max(year)-1 gets de max year of all selected years in filter panel, it doesn't take into account the value of the dimension in bar chart. That is, the mentioned measure is the same as:

Let's say theese are the sales:

• 2022--> 100
• 2021--> 95
• 2020--> 90
• 2019--> 78

So the measures are:

• 2022-->100-90
• 2021--> 90-90
• 2020--> 95-90
• 2019--> 78-90

Because max(year)-1 is always the measure of 2020, because max(year) is always 2021, regardless of the dimension value.

I wish there is a way to get de previous year given a dimension value, and not for the whole loaded data.

How can I solve this? Is there a way to get max(year) inside set analysis as de max year of the current year in dimension?

Thanks!

[edwin · Accepted solution]

i would implement the relationship in the script to make the UI faster and easier to maintain:

data:
load * inline [
YearDim, Sales
2022,100
2021,95
2020,90
2019,78
2022,10
2021,9
2020,9
2019,7
];


tmpYears:
load distinct YearDim
Resident data;

inner join (tmpYears)
load YearDim as Year
resident tmpYears;

NoConcatenate
Years:
load Year, YearDim, if(Year=YearDim, 'CURRENT','PREVIOUS') as YearType
resident tmpYears
where Year=YearDim or Year=YearDim+1;

drop table tmpYears;

in your table:

[vinieme12]

This will be as below 

Use Year as dimension

And Measure 

=Sum(Sales) -Above(Sum(Sales))

https://community.qlik.com/t5/Qlik-Design-Blog/The-Above-Function/ba-p/1465357

https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/Inter...

[amaaiia]

It works!