Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
david_pearson
Contributor III
Contributor III

Set analysis max date -1

Hi all

have have a dataset with a date field (dd/mm/yyyy format). This, depending on the load date of the data, will show the last date in that quarter. i.e. loaded in any date in Q1 2021 will be 31/03/2021, Q4 2020 is 31/12/2020 etc. This field is called LOAD_DATE and is part of my autocalendar.

I have set analysis to calculate based on the max LOAD_DATE field which appears to work. I want to do the same but for the next available month before the max load date. In this case the max is 31/03/2021, I want the set analysis to go against 31/12/2020. Obviously I want this to change as new data loads in.

 

Current formula for max date is below. i had thought i could just add -1 but this goes back 1 day. 

 

if(count(distinct {<[LOAD_DATE.autoCalendar.Date] = {"$(=Max([LOAD_DATE.autoCalendar.Date]))"}>} PH_REF)=0,0,
sum(aggr(count(distinct {<[LOAD_DATE.autoCalendar.Date] = {"$(=Max([LOAD_DATE.autoCalendar.Date]))"}>}COVER_CODE),PH_REF))/
sum(aggr(count(distinct{<[LOAD_DATE.autoCalendar.Date] = {"$(=Max([LOAD_DATE.autoCalendar.Date]))"}>} PH_REF),PH_REF)))

 

thanks 

1 Solution

Accepted Solutions
brunobertels
Master
Master

Hi 

To get the previous month : 

addmonth(Max([LOAD_DATE.autoCalendar.Date]),-1) : 

31/03/2021 will become 28/02/2021 

in your case as the previous month seems to be the las month of previous quarter use 

quarterend() function : 

quarterend(Max([LOAD_DATE.autoCalendar.Date]),-1) will give you 31/12/2020 

 

regards 

View solution in original post

1 Reply
brunobertels
Master
Master

Hi 

To get the previous month : 

addmonth(Max([LOAD_DATE.autoCalendar.Date]),-1) : 

31/03/2021 will become 28/02/2021 

in your case as the previous month seems to be the las month of previous quarter use 

quarterend() function : 

quarterend(Max([LOAD_DATE.autoCalendar.Date]),-1) will give you 31/12/2020 

 

regards