topic Re: Show data from date range of current and previous year in App Development

Show data from date range of current and previous year

Realmoca — Fri, 02 Apr 2021 08:19:02 GMT

Hello everyone,

Lately I'm having this issue whenever someone selects an specific date or date range from this year in any of the charts from the dashboard that supposedly have to show the same data but from the previous year.

I don't know why but at the beginning it was working fine (only when I selected a year from the year filter), but but now it doesn't seems work anymore.

ejemplo 1

The field that I'm using fot the Year filter is this one: =[his_date.autoCalendar.Year]

This is the expression that I'm using to calculate the values:

=Sum ( {$<hist_y_otb={"History"}, [his_date.autoCalendar.Year]={$(#=Max(Year)-1)}>} quantity_rooms )

Also, when I don't have any filter selected it gaves me the desired result (2020 / 2019):

ejemplo 3

In the other when I select only one month or one date, I get this result:

ejemplo 2

I would like it to show the data from the same dates of the year/period selected and the previous year. How can I accomplish this?

Thanks for the help!!

Re: Show data from date range of current and previous year

srdheekonda — Fri, 14 Feb 2020 07:39:07 GMT

I think as your filters are date level and your expression should be filtered at date level to solve your problem.

I tried some formulas at my end may be it will work for you. I applied date format to make my formula work in my case. apply date format according to your requirement.

another thing is CY YTD made to take dynamic values ($) and PY YTD applied constant selection (1).

Hope this helps:

CY YTD:

SUM({$<his_date={">=$(=DATE(YearStart(Max(his_date)),'YYYY-MM-DD'))<=$(=Max(his_date))"}>} quantity_rooms)

PY YTD:

SUM({1<his_date={">=$(=DATE(AddYears(YearStart(Max(his_date)),-1),'YYYY-MM-DD'))<=$(=DATE(AddYears(Max(his_date),-1),'YYYY-MM-DD'))"}>} quantity_rooms)

Re: Show data from date range of current and previous year

Realmoca — Fri, 14 Feb 2020 21:19:17 GMT

Hello Srdheekonda,

Thank you very much for your prompt answer!

I tried with the recommendations that you gave me and it didn't seems to work. I don´t know why because I think that your approaching is the right one, it has to be related to data level and the expression that I'm using should be filtered at date level.

example 4

I don't know if it's important but I'm using the generated auto calendar fields to filter for year ([his_date.autoCalendar.Year]), quarter ([his_date.autoCalendar.Quarter]) and month ([his_date.autoCalendar.Month]) from the field his_date.

I have reviewed the forum and some websites and tried to use the recommendations and solutions, but I couldn't make it work. There is one the As of Table but I don't know how to apply it 😞

Please let me know if you need me to share anything more like more info.

Thanks!

Re: Show data from date range of current and previous year

srdheekonda — Wed, 19 Feb 2020 06:19:58 GMT

I think you can use AutoCalendarDate and format accordingly if there is any issues with Date Format.