topic Re: Duplicate YearMonth date in QlikView

Duplicate YearMonth date

Fri, 25 Jan 2013 15:27:44 GMT

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

Re: Duplicate YearMonth date

Fri, 25 Jan 2013 15:34:59 GMT

Try using;

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

It works even with sorting.

Best.

Re: Duplicate YearMonth date

Fri, 25 Jan 2013 15:42:41 GMT

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

Re: Duplicate YearMonth date

Gysbert_Wassenaar — Fri, 25 Jan 2013 15:48:10 GMT

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.

Re: Duplicate YearMonth date

Fri, 25 Jan 2013 15:49:07 GMT

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.

Re: Duplicate YearMonth date

nagaiank — Fri, 25 Jan 2013 15:49:41 GMT

You may use

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

Re: Duplicate YearMonth date

Fri, 25 Jan 2013 16:16:27 GMT

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