Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
himatech
Contributor III
Contributor III

Master Calendar Not Reading Date Properly

Hi,

Using the Master Calendar to load the date field in the form of Day, Week, Year, etc shows a dash in some records while if I create an additional dimension for that record using the Day(), Week(), or Year(), then it works. Is there something wrong in my script?

LOAD

    "OPEN_TIME" as PROpenTime,

    Date( Floor( "OPEN_TIME" ) ) as PROpenDate;

SQL SELECT * FROM databasename;

// The Master Calendar

Load

     PROpenDate,

     week( PROpenDate ) As PROpenDateWeek,

     Year( PROpenDate ) As PROpenDateYear,

     Month( PROpenDate ) As PROpenDateMonth,

     Day( PROpenDate ) As PROpenDateDay,

     YeartoDate( PROpenDate )* -1 as PROpenDateCurYTDFlag,

     YeartoDate( PROpenDate , -1) * -1 as PROpenDateLastYTDFlag,

     date( monthstart( PROpenDate ), 'MMM-YYYY' ) as PROpenDateMonthYear,

     ApplyMap( 'QuartersMap' , month( PROpenDate ), Null() ) as PROpenDateQuarter,

     Week( weekstart( PROpenDate ) ) & '-' & WeekYear( PROpenDate ) as PROpenDateWeekYear,

     WeekDay( PROpenDate ) as PROpenDateWeekDay

Resident themaintable;

Here is a sample of the outcome:

qlikview-20170404.png

You will notice that the second record is working properly while the first one is not.

Thank you for your response in advance.

45 Replies
himatech
Contributor III
Contributor III
Author

Here you go...

Kushal_Chawda

you had written the wrong expression

Wrong:

=Count({$ <PROpenDateMonthYear = {"$(=Date( Max( PROpenDateMonthYear ),'MMMM-YYYY'))"}>}ID)

This would have worked when MonthYear values were like January-2017 etc

Corrected:

=Count({$ <PROpenDateMonthYear = {"$(=Date( Max( PROpenDateMonthYear ),'MMM-YYYY'))"}>}ID)

You need to format as per the values, eg for Jan-2017 format should be MMM-YYYY

himatech
Contributor III
Contributor III
Author

Yes, you are right but the results are still showing 7 while it should be 3 only.

Kushal_Chawda

use below expression

=Count(DISTINCT{$ <PROpenDateMonthYear = {"$(=Date(AddMonths(max(PROpenDateMonthYear),-1),'MMM-YYYY'))"}>}ID)

himatech
Contributor III
Contributor III
Author

The result is 1 not 3.

Kushal_Chawda

I am getting 3. Are you using same expression, it is previous month expression

Kushal_Chawda

see attached

himatech
Contributor III
Contributor III
Author

I think I made a mistake. I tried again and the counting is working. Thanks a lot 🙂

I'll do more testing and I'll let you know of the results.

Kushal_Chawda

Please mark the answer as correct if it is working fine to close the thread.

himatech
Contributor III
Contributor III
Author

Sure.. I will try to do that by today 🙂

Thanks a lot!