Qlik Community

Qlik Sense Cloud Discussions

ClickInsight
New Contributor

Get Last Three Months with No Master Calnder

Hi Community,

I'm working with some client data/set-up and they haven't created a master calendar and have create a "month" dimension that's formatted as YYYY-MM.

I'm trying to pull the last three months of unique email opens but don't seem to be able to get it to work. 

Note creating custom dimensions and measure is locked-down so I can't do that. 

The expression I'm currently using is:

=sum({<Month={'>=(=Month(Max(Date)), -3)<=(=Date(Max(Date)))'}>}[Unique Opens])

That doesn't work but when I use specific date, like below, it works for specific month.

=sum({$<Month={"2018-08"}>}[Unique Opens])

Thanks in advance for any help/suggestions. 

Kirk

7 Replies
MVP
MVP

Re: Get Last Three Months with No Master Calnder

I think this should work for you:

=Sum( { < Month = { ">=$(=Date(AddMonths(Max(Date), -3),'YYYY-MM'))<=$(=Date(Max(Date),'YYYY-MM'))"}>} [Unique Opens] )

EDITED:

Actually the greater than and less than in my above answer doesn't work exactly as I expect and I dont't know the reason...

However this
should work instead:

 

Generate a list of months with the ValueLoop() synthetic value generator that can be aggregated with the Concat():

='[' & Concat( '$' & '(=Date( AddMonths( Max(Date),' & (-ValueLoop(0,2))& '),''YYYY-MM''))' ,'],[') & ']'

 

Or generate the list of months manually:

='[' & Date(AddMonths(Max(Date),-2),'YYYY-MM') & '],[' & Date(AddMonths(Max(Date),-1),'YYYY-MM') & '],[' & Date(AddMonths(Max(Date),0),'YYYY-MM') & ']'

 

 

MVP
MVP

Re: Get Last Three Months with No Master Calnder

I think this should work for you:

=Sum( { < Month = { ">=$(=Date(AddMonths(Max(Date), -3),'YYYY-MM'))<=$(=Date(Max(Date),'YYYY-MM'))"}>} [Unique Opens] )

EDITED:

Actually the greater than and less than in my above answer doesn't work exactly as I expect and I dont't know the reason... 

However this 
should work instead:

 

Generate a list of months with the ValueLoop() synthetic value generator that can be aggregated with the Concat():

=Sum({<Month={$(=
'[' & Concat( '$' & '(=Date( AddMonths( Max(Date),' & (-ValueLoop(0,2))& '),''YYYY-MM''))' ,'],[') & ']'
)}>} [Unique Opens])

 

Or generate the list of months manually:

=Sum({<Month={$(=
'[' & Date(AddMonths(Max(Date),-2),'YYYY-MM') & '],[' & Date(AddMonths(Max(Date),-1),'YYYY-MM') & '],[' & Date(AddMonths(Max(Date),0),'YYYY-MM') & ']'
)}>} [Unique Opens])

 

 

MVP
MVP

Re: Get Last Three Months with No Master Calnder

Generate a list of months with the ValueLoop() synthetic value generator that can be aggregated with the Concat():

=Sum({<Month={$(=
'[' & Concat( '$' & '(=Date( AddMonths( Max(Date),' & (-ValueLoop(0,2))& '),''YYYY-MM''))' ,'],[') & ']'
)}>} [Unique Opens])

 

Or generate the list of months manually:

=Sum({<Month={$(=
'[' & Date(AddMonths(Max(Date),-2),'YYYY-MM') & '],[' & Date(AddMonths(Max(Date),-1),'YYYY-MM') & '],[' & Date(AddMonths(Max(Date),0),'YYYY-MM') & ']'
)}>} [Unique Opens])

ClickInsight
New Contributor

Re: Get Last Three Months with No Master Calnder

Thanks! I'll give that a go. I don't have access to script load (also locked-down 😉 but I'll just try using it directly in the expression editor. 

Cheers. 

MVP
MVP

Re: Get Last Three Months with No Master Calnder

It is actually mean to be used in the UI - not in the load script. You can't use set expressions in the load script so my suggestions would be useless for that purpose.

MVP
MVP

Re: Get Last Three Months with No Master Calnder

Your expression was almost there, but you need double quotes and an AddMonths call:

=sum({<Month={">=(=AddMonths(Max(Date), -3))<=(=Date(Max(Date)))"}>} [Unique Opens])
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
ClickInsight
New Contributor

Re: Get Last Three Months with No Master Calnder

Thanks for the help. Unfortunately it's still not returning any values when I use either expression. 

One thing I think is an issue is "Month" has been used as a field therefore if I try to use it as a function is doesn't work. 

That being said, I'd think that if I use the field "Month" I should still be able to call back the last three months. 

I could use another field "Campaign Start Date" but it's formatted as a string. Where it's MM/DD/YYYY HH:MM:SS DD formatted. Thinking I can use the Date# to convert it to date and then call last three months that way.

Another alternative I'm thinking about it just calling back the last three months of "Campaign Start Date" and then leaving the Unique Opens field as =sum([unique open])

Thanks in advance.