Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
See why Qlik is recognized as a Leader for the 10th year in a row – and discover how Qlik can help put your business in the lead. Get Report
Highlighted
Realmoca
New 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!!

3 Replies
Highlighted
srdheekonda
New Contributor III

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

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)

Highlighted
Realmoca
New Contributor II

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

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!

Highlighted
srdheekonda
New Contributor III

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

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