Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Realmoca
Contributor II
Contributor II

Show data from date range of current and previous year

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 1ejemplo 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 3ejemplo 3

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

ejemplo 2ejemplo 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!!

Labels (1)
3 Replies
srdheekonda
Contributor III
Contributor III

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)

Realmoca
Contributor II
Contributor II
Author

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 4example 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!

srdheekonda
Contributor III
Contributor III

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