topic First date from YearMonth field in QlikView

First date from YearMonth field

Anonymous — Tue, 28 Jul 2015 12:27:13 GMT

Good day!

Is any way to get first date for every YearMonth?

In model i have only one period field YearMonth

I need to get such values:

YearMonth Date

201401 01.01.2014

201402 01.02.2014

201507 01.07.2015

....

Thanks.

Re: First date from YearMonth field

Anonymous — Tue, 28 Jul 2015 12:30:32 GMT

use monthstart function

Re: First date from YearMonth field

Tue, 28 Jul 2015 12:32:53 GMT

try this:

MakeDate(Year, Month, 1) as Date

Re: First date from YearMonth field

Anonymous — Tue, 28 Jul 2015 12:33:46 GMT

MonthStart(YearMonth) as Date?

it's working incorrectly

Re: First date from YearMonth field

sunny_talwar — Tue, 28 Jul 2015 12:34:07 GMT

Try this in the front end:

=Date(MakeDate(Year(Date#(YearMonth, 'YYYYMM')), Month(Date#(YearMonth, 'YYYYMM')), 1), 'DD.MM.YYYY')

or this in the back end:

Date(MakeDate(Year(Date#(YearMonth, 'YYYYMM')), Month(Date#(YearMonth, 'YYYYMM')), 1), 'DD.MM.YYYY') as Date

Re: First date from YearMonth field

sasiparupudi1 — Tue, 28 Jul 2015 12:35:00 GMT

=Date(monthstart(date#(YearMonth,'YYYYMM')),'DD.MM.YYYY')

Re: First date from YearMonth field

Anonymous — Tue, 28 Jul 2015 12:35:23 GMT

use makedate function

Re: First date from YearMonth field

MK_QSL — Tue, 28 Jul 2015 12:42:25 GMT

=MonthStart(Date#(YourMonthYearField,'YYYYMM'))

Re: First date from YearMonth field

Anonymous — Tue, 28 Jul 2015 13:11:30 GMT

Thank you!!!

Could you please write me, how to write flags:

1. YTD (from beginning of current year till max yera month)

2. PriorYTD (The same as YTD, but for prior year)

3. MTD (get max YearMonth)

4. PriorMTD (The same as MTD, but for prior year)

Re: First date from YearMonth field

sunny_talwar — Tue, 28 Jul 2015 13:25:51 GMT

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

If(TempDate >= MonthStart(Today()) and TempDate <= Today(), 1, 0) as CurMTDFlag,

If(TempDate >= AddYears(MonthStart(Today()), -1) and TempDate <= AddYears(Today(), -1), 1, 0) as LastMTDFlag,

Re: First date from YearMonth field

Anonymous — Tue, 28 Jul 2015 13:33:17 GMT

Thanks.. but LastYTDFlag works not for my need.

For example:

if max YearMonth is 201503 = LastYTDFlag must include 201401,201402,201403

And what about MTD and PriorMTD?

Re: First date from YearMonth field

sunny_talwar — Tue, 28 Jul 2015 13:35:01 GMT

Updated my last response:

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

If(TempDate >= MonthStart(Today()) and TempDate <= Today(), 1, 0) as CurMTDFlag,

If(TempDate >= AddYears(MonthStart(Today()), -1) and TempDate <= AddYears(Today(), -1), 1, 0) as LastMTDFlag,

Here you can replace Today() with the variable that determines the max date in your data set.