topic Re: working with dates in QlikView

working with dates

ecabanas — Thu, 15 Oct 2015 14:18:23 GMT

Hi guys,

I want to do a calculation and with a date condition, but as usual i have a format date problem,

Count(DISTINCT{<[SALESID]={'PV0***'},

FirstOrder_month=CalendarMonthandYear,

Order_Number={'1'},ESTADO_PEDIDO=>} CUSTACCOUNT)

FirstOrder_month is formated DD/MM/YYY for example: 30/09/2015

CalendarMonthandYear is sept-2015

Anyone can help to me to equalize the data

many thank's guys

Eduard

Re: working with dates

Oleg_Troyansky — Thu, 15 Oct 2015 14:25:15 GMT

Hi Eduard,

first of all, if your CalendarMonthandYear is a string field, you should transform it into a proper Date field, even if you want it to be formatted as "Sep-2015". I'm not sure you can make it look like "Sept-2015", because date format MMM returns a 3-character name, and MMMM returns the full month name, e.g. September.

Once you formatted the field as a date field, you can use the Advance Search syntax to overcome the difference in formatting. Something like this should work:

FirstOrder_month={"=MonthStart(FirstOrder_month)=MonthStart(CalendarMonthandYear)"}

Check out my new book QlikView Your Business - in it, I describe in detail how to work with Dates in Set Analysis, and how to use Advance Search for various purposes.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: working with dates

jduarte12 — Thu, 15 Oct 2015 14:25:33 GMT

Hello.

I think this post may help you:

The Date Function

Regards,

João Duarte

Re: working with dates

Digvijay_Singh — Thu, 15 Oct 2015 14:30:44 GMT

Something like this may be -

Count(DISTINCT{<[SALESID]={'PV0***'},

FirstOrder_month={'$(=Date(MonthStart(Date#(purgechar(CalendarMonthandYear,'t'),'MMM-YYYY')),'DD/MM/YYYY))'},

Order_Number={'1'},ESTADO_PEDIDO=>} CUSTACCOUNT)

Re: working with dates

ecabanas — Thu, 15 Oct 2015 16:08:46 GMT

Hi guys

Maybe I explained wrong,

Count(DISTINCT{<[SALESID]={'PV0***'},

FirstOrder_month=Monthend,

Order_Number={'1'},ESTADO_PEDIDO=>} CUSTACCOUNT)

FirstOrder_month is formated DD/MM/YYY and monthend

CalendarMonthandYear is YYY/MM/DD