Set analysis greater than with multiple date fields used in set analysis

[ibradly]

Hi,

I am trying to get data before 2019-05-31 with my YTD formula. 

My current YTD formula works and it is the following:

sum({$<Category={'Brand'},Device={'Desktop'},WeekStart=,MonthYear=,Year={"$(=Max(Year))"},Date={"<=$(=max(Date))"}>}Clicks) 

Now when i want to extract before 2019-05-31 the set analysis doesn't work anymore. However, when I do greater than it works. The highlight in red is what i added to the set analysis.

sum({$<Category={'Brand'},Device={'Desktop'},WeekStart=,MonthYear=,Year={"$(=Max(Year))"},Date={"<=$(=max(Date))"}, Date={">=$(=Only(2019-05-31))"}>}Clicks)

My theory is that it is not work because I am using max(date) before therefore it wont work.

The reason i want to get before May 31st is because after that date the estimated conversion % changed.

Can someone help me to get the current YTD to date for before May 31st. ?

[john_obrien · Accepted solution]

ibradly,

I think you are on the right track to use only one field selection for Date in set expression.  You won't need both 

Date={"<=$(=max(Date))"} and Date={">=$(=Only(2019-05-31))"}

The Date part like this should work - Date={"< =dateformatted"}>}.  Date formats can be tricky in Qlik.

Can you try this expression?

sum({$<Category={'Brand'},Device={'Desktop'},WeekStart=,MonthYear=,Year={"$(=Max(Year))"}, Date={"<2019-05-31"}>} Clicks)

[Vegar]

You need to make sure that your application date format is YYYY-MM-DD AMsnd you need to define your input as a string like this:

Date={">=$(=Only('2019-05-31'))"}

[ibradly]

Hi @john_obrien ,

Thanks for providing your answer I ended up using your date function instead of mine but i still included Date={"<=$(=max(Date))"}  which worked!

Thanks!