Duplicate YearMonth date

[#1]

Hello Everyone,

I make a date by using date(PostDate, 'YYYY/MM') as PostYearMonth. I use PostYearMonth to create a list box. The List Box shows duplicate row. Can you help.

Please see attached picture for detail.

Thanks in advance,

Dust

[#2]

Try using;

Year(PostDate)&'-'&Month(PostDate)

It works even with sorting.

Best.

[#3]

Thank you, Anil!

We are getting closer. I want to show the Year-Month like 2012-01 or 2012/01. Current one show like 2012-Jan. Or looks like sorting not work.

Thank you veyr much.

Dust

[Gysbert_Wassenaar]

Create the extra fields in the script. Actually use Rob Wunderlich's Qlikview Components library to create a master calendar for you based on your date field. See here for information.

[#5 · Accepted solution]

Then you should add a calender to your document and connect your date to it.

When you load your table load your date as;

num(Year(PostDate))*10000+num(Month(PostDate))*100+num(day(PostDate)) as PostDate

Select a minimum date for your doucment;

let vMinDate = num('01.01.2012');

let vMaxDate = num(today());

TempCalender:

load

date($(vMinDate)+iterNo()-1) as TempDate

AutoGenerate(1) while $(vMinDate)+iterNo()-1 <=$(vMaxDate);

Calender:

load

TempDate as Date,

num(Year(TempDate))*10000+num(Month(TempDate))*100+num(day(TempDate)) as PostDate,

Month(TempDate) as Month,

Day(TempDate) as Day,

Year(TempDate) as Year,

week(TempDate) as Week,

date(MonthStart(TempDate),'MM-YYYY') as YearMonth,

WeekDay(TempDate) as WeekDay

Resident TempCalender;

DROP TABLE TempCalender;

And use your calender for dates.

Best.

[nagaiank]

You may use

Year(PostDate)&'-'&Num(Month(PostDate),'00')

[#7]

Thank you, Anil, Krishnamoothy and Gysbert. You are great.