Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
ibradly
Contributor III
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 (3)
1 Solution

Accepted Solutions
john_obrien
Contributor III
Contributor III

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
Contributor III
Contributor III

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
MVP
MVP

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
Contributor III
Contributor III
Author

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!