topic Using Date in Set Analysis in QlikView

Using Date in Set Analysis

Wed, 18 May 2011 13:14:44 GMT

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

Using Date in Set Analysis

erichshiino — Wed, 18 May 2011 13:22:41 GMT

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

Using Date in Set Analysis

Wed, 18 May 2011 14:32:49 GMT

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