Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Display Previous year data with year as dimension

Hi,

I would like to create a Bar Chart with  dimension Year  and a measure showing the previous year  sum of  [Value]  meaning  sum({ previous year} [Value])

for example I expect to see for 2019 bar the sum for 2018, for 2018 bar the sum for  2017 and so on. 

I tried many options, but it doesn't work well.
the same worked for me before with other dimensions.

how can this be done? 

 

*I actually need to display the change percentage between the year and the year before,   i was trying to simplify the issue.   

 

Thank you!

 

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

this could not work.

in your bar chart you have all years so you can calculate with all years.

If you are selecting year 2018-2020, you only will see calculations for year 2019 and 2020, for year 2017 there are no values for (excluded by selection)

So if you are selection year 2019 there are only values for year 2019, no values from 2018 to compare.

But you can use:

if (getselectedcount(Year)=1,

sum(Value)/sum({<Year={$(=max(Year)-1))}>} Value),

sum(Value)/above(sum(Value))

)

In that case, is year is selected, the value in month or quarter is compared with month or quarter from selected Year -1

If no Year is selected, the existing expression is used.

Regards

View solution in original post

10 Replies
MVP
MVP

You should try using above() instead.

Highlighted
Contributor
Contributor

Thank you for your replay

The sum for each year is made of multiple records, my data looks something like this:

EmpIDComponentIDDateValue
1131/12/2018100
1231/12/2018100
1331/12/2018100
1231/12/2019100
1331/12/2019100

 

to use above() i would need to have one record for year ? or how can i use it like this? 

thank you

Highlighted
Partner
Partner

don't use it in script, use it in bar chart

it works:

sum_above.JPG

first expression sum(Value)

second expression above(sum(Value)

third expression sum(Value)/above(sum(Value))

Regards

Highlighted
Contributor
Contributor

works great for years thanks both !

I would like to drill down to Quarter/Month level but still have comparison to same period last year
with above() i get last quarter/month.

I also tried using the offset - resulted in no data.

how can i do this? 

Highlighted
Partner
Partner

this could not work.

in your bar chart you have all years so you can calculate with all years.

If you are selecting year 2018-2020, you only will see calculations for year 2019 and 2020, for year 2017 there are no values for (excluded by selection)

So if you are selection year 2019 there are only values for year 2019, no values from 2018 to compare.

But you can use:

if (getselectedcount(Year)=1,

sum(Value)/sum({<Year={$(=max(Year)-1))}>} Value),

sum(Value)/above(sum(Value))

)

In that case, is year is selected, the value in month or quarter is compared with month or quarter from selected Year -1

If no Year is selected, the existing expression is used.

Regards

View solution in original post

Highlighted
Contributor
Contributor

thank you, looks to be working great like this!

and especially thank you for explaining 🙂 
I understand better how it works now

Highlighted
Contributor
Contributor

@martinpohl 

I still have issue with month, where i want to compare current month with previous month.

it works great with above() for all months except for January where i would like to compare to December of previous year . 
i understand that it won't work with above since December of last year is not within the selection.

It also doesn't work with set expression since Dimension value is January and the data is for December. 

Can this be achieved? 

thank you

Highlighted
Partner
Partner

Yes you could.

It look something like

above(sum({<Date={">=$(=monthstart(addmonths(makedate(max(Year)),-1)))"}, Year= >} Value))

In words:

take the max selected year, eg 2020

make it to a date = 1/1/2020

add a minus month = 12/1/2019

took the monthstart = 12/1/19

To achieve that you take values with date Dec 2019 although year 2020 is selected you have to excluse selection in year 

Regards

Highlighted
Contributor
Contributor

thanks for your response, but that's not working so well. 
@martinpohl 


the expression does display the previous month for every month except January and it added a column for Dec (in 2020 selection) with  the data of the previous column (April 2020 -  the last data in the model)

to my understanding above() is used To get values from other rows within the same chart.
but December 2019 is not within the scope of the chart when selecting year 2020.