topic Re: create two different SET DateFormat ? in QlikView

create two different SET DateFormat ?

stevelord — Wed, 07 Jan 2015 20:59:10 GMT

Hi, I want to make a date format for MMM-YYYY (Jan-2015) for use in some listboxes and still keep the other M/D/YYYY date format for use in field values. Can someone provide me the line to add below for MMM-YYYY date format that wouldn't replace or conflict with the default date format? Previously, I'd been doing weird concatenate month() year() functions in script and would like to quit that.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET ____________='MMM-YYYY', //what goes in this blank?

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Thanks!

Re: create two different SET DateFormat ?

rwunderlich — Thu, 08 Jan 2015 01:24:15 GMT

You can use whatever you want for the variable name -- as long as it does not conflict with a special QV variable name.

The variable "DateFormat" is a special QV name that defines the default format for dates. You can define additional variables like

SET vMyDateFormat = 'MMM-YYYY';

The vMyDateVariable will have no impact or meaning until it is used in an expression like:

=date(OrderMonth, '$(vMyDateFormat'))

Because variables cannot be used as format string in the chart Number format dialogs, it's not typical to set up exception date formats in variable strings.

-Rob

Re: create two different SET DateFormat ?

jagan — Thu, 08 Jan 2015 02:06:50 GMT

Hi,

Try like this, create a separate Month-Year column in script like below

LOAD

Date(OrderMonth, 'MMM-YYYY') AS MonthYear

FROM DataSource;

Now use MonthYear field as filter.

Hope this helps you.

Regards,

Jagan.

Re: create two different SET DateFormat ?

stevelord — Thu, 08 Jan 2015 17:16:31 GMT

Thanks for the array of insights, Rob. It looks like I had slightly the wrong question/approach, and that the OrderDate part of the Date function was the gap in my knowledge I needed to fill in. (Someone else chimed in with that part after you too, and I'll tag them for helpful answer points.)

Re: create two different SET DateFormat ?

stevelord — Thu, 08 Jan 2015 17:17:21 GMT

Thanks! Two correct answers related to DatE(OrderDate, ) approach, just had to give a helpful nod to the second one in the cue.

edit: Date(OrderMonth, ) even

Re: create two different SET DateFormat ?

rwunderlich — Thu, 08 Jan 2015 17:18:34 GMT

I think Jagan meant:

Date(MonthStart(OrderDate), 'MMM-YYYY') AS MonthYear