Display Previous year data with year as dimension

[Lital]

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!

[tresesco]

You should try using above() instead.

[Lital]

Thank you for your replay

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

[martinpohl]

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

[Lital]

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? 

[martinpohl · Accepted solution]

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

[Lital]

thank you, looks to be working great like this!

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

[Lital]

@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

[martinpohl]

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

[Lital]

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.