topic Re: Display Previous year data with year as dimension in App Development

Display Previous year data with year as dimension

Lital — Sat, 16 Nov 2024 02:20:04 GMT

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!

Re: Display Previous year data with year as dimension

tresB — Tue, 26 May 2020 07:47:25 GMT

You should try using above() instead.

Re: Display Previous year data with year as dimension

Lital — Tue, 26 May 2020 08:10:22 GMT

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

Re: Display Previous year data with year as dimension

martinpohl — Tue, 26 May 2020 08:20:08 GMT

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

Re: Display Previous year data with year as dimension

Lital — Tue, 26 May 2020 08:40:57 GMT

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?

Re: Display Previous year data with year as dimension

martinpohl — Tue, 26 May 2020 08:51:54 GMT

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

Re: Display Previous year data with year as dimension

Lital — Tue, 26 May 2020 09:13:28 GMT

thank you, looks to be working great like this!

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

Re: Display Previous year data with year as dimension

Lital — Wed, 27 May 2020 10:03:43 GMT

@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

Re: Display Previous year data with year as dimension

martinpohl — Wed, 27 May 2020 10:53:51 GMT

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

Re: Display Previous year data with year as dimension

Lital — Sun, 31 May 2020 09:15:48 GMT

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.

Re: Display Previous year data with year as dimension

martinpohl — Tue, 02 Jun 2020 06:54:01 GMT

That's right, the expression only shows previous value.

But you can add actual value.

See example

and the value in column 2 in above-expression is december value