Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
ibradly
New Contributor III

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

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. ?

 

 

Labels (4)
1 Solution

Accepted Solutions
john_obrien
New Contributor III

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

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)

View solution in original post

3 Replies
john_obrien
New Contributor III

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

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)

View solution in original post

Partner
Partner

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

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'))"}

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
ibradly
New Contributor III

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

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!