# App Development

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for
Did you mean:
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 (1)
• ### QlikSense Measures

1 Solution

Accepted Solutions
Partner - Master

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

10 Replies
MVP

You should try using above() instead.

Contributor
Author

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

 EmpID ComponentID Date Value 1 1 31/12/2018 100 1 2 31/12/2018 100 1 3 31/12/2018 100 1 2 31/12/2019 100 1 3 31/12/2019 100

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

thank you

Partner - Master

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

it works:

first expression sum(Value)

second expression above(sum(Value)

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

Regards

Contributor
Author

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?

Partner - Master

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

Contributor
Author

thank you, looks to be working great like this!

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

Contributor
Author

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

Partner - Master

Yes you could.

It look something like

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

Contributor
Author

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.

Tags
Community Browser