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

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using Date in Set Analysis

I am trying to use a set analysis as an expression where I need to limit the select to a specific month. I cant seem to get the date syntax correct.

I used the below script to change the desired date from dd/mm/yyyy to MM-YYYY

Date(monthstart(CloseDate), 'MM-YYYY') as CloseDate_MY;

This part works fine.....but now that the date is in that format...how do you write the desired month in the set analysis expression. My expression is below:

sum({ $<CloseDate_MY={052011}>} Opportunity.Amount) ( I need to limit the date to May 2011

Thanx

2 Replies
erichshiino
Partner - Master
Partner - Master

I guess you need to change the script to:

Date(monthstart(CloseDate), 'MMYYYY') as CloseDate_MY

or the set analysis to sum({ $<CloseDate_MY={05-2011}>} Opportunity.Amount)

or you can break the date in two fields:

year(CloseDate) as CloseYear,

num(month(CloseDate)) as CloseNMonth

and set analysis to:

sum({ $<ClsoeYear={2011}, CloseNMonth={5}>} Opportunity.Amount)

Hope it helps,

Erich

Not applicable
Author

I would advice to create a Flag in the script named "ClosedDtFlag" and marked it against the Date/Month/Year with value as 1or 0 and in expression you can simply write as

Normal Expression:-

SUM(Opportunity.Amount*ClosedDTFlag) or

Set Analysis:-

SUM({<ClosedDTFlag={1}> Opportunity.Amount})

Hope this helps