topic Re: Date format in set analysis in QlikView

Date format in set analysis

Fri, 06 Nov 2015 10:04:47 GMT

Hi Guys,

Currently I have a set analysis selecting dates in a expression e.g.

Count({$<DateStart_User = {">=$(=Date(MonthStart(Addmonths(Today(),-1))))"}>} Employee)

and I have a date expression so that my chart can give data over MMYYYY format:

=Date(MonthStart(DateStart_User), 'MMYYYY')

My question is, is there a way I can force the 'MMYYYY' format in my set analysis expression, so that I can only use DateStart_User as a Dimension?

Please advise, and thanks in advance.

swuehl — Fri, 06 Nov 2015 12:42:06 GMT

I think I haven't fully understood your issue, but maybe it's a good start to create a field in the data model:

LOAD

DateStart_User,

Date(MonthStart(DateStart_User), 'MMYYYY') as MonthStart_User,

...

Then you can use this field as dimension, and in your set expression

Count({$<MonthStart_User = {">=$(=Date(MonthStart(Addmonths(Today(),-1))),'MMYYYY')}>} Employee)

Anonymous — Fri, 06 Nov 2015 12:45:42 GMT

You can do while loading your data in script:

date(DateStart_User,'MMYYYY') as DateStart_User;

and then In expression you can simply use:

Count({$<DateStart_User= {">=$(=Date(MonthStart(Addmonths(Today(),-1))),'MMYYYY')}>} Employee)

Mon, 09 Nov 2015 08:28:33 GMT

Thank you.