Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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:
So the measures are:
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!
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:
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:
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
It works!