Date Issue Reading Date Format

[hammermill21]

Hello,

I have dates that are 01/10/2018 and 1/12/2018 so depending on what selection I make it's not reading both. How can I fix that?

Thank you!

[hammermill21]

So when I select all Jan dates:

I get an inventory count of 261, which is correct.

Now if I select just the "month" Jan:

I get an inventory count of 42, so I'm guessing it's not grabbing all the Jan dates when I select Month Jan.

[sunny_talwar]

Two questions

1) Are you sure you selected all Jan dates? I can't see the whole filter object... so can't really tell

2) What is the expression you have used?

[hammermill21]

Yes, I have selected all dates, I just checked again.

For the Date, my expression is just Get Date; for the Month I have expression Month.

Then for my inventory count, my expression is:

Count({<[Bascom Item Type]={'FIRE EXTINGUISHER'},[Bascom Pass]= {'YES'}>} [Bascom Item Type])

I am counting how many Fire Extinguishers there are that have Passed Inspection.

[sunny_talwar]

Don't really know... it should work...

When you select Month... what dates get selected?

[hammermill21]

It selects most dates in Jan except 01/04/2018, 01/05/2018 and 01/07/2018. It's so odd!!

And I don't see anything different in the data I am pulling:

Example of one recorded dates 01/07/2018:

<Inspection_x0020_Date>01/07/2018</Inspection_x0020_Date>

<Inspection_x0020_Time>02:33 PM</Inspection_x0020_Time>

Example of data record dated 01/31/2018:

<Inspection_x0020_Date>01/30/2018</Inspection_x0020_Date>

<Inspection_x0020_Time>07:13 AM</Inspection_x0020_Time>

[sunny_talwar]

Can you share your complete script...

[hammermill21]

Of Course:

RestConnectorMasterTable:

SQL SELECT

"Barcode",

"Username",

"Inspection Date",

"Inspection Time",

"Item Type",

"Building",

"Floor",

"Location",

"Room Number",

"Description",

"Comments",

"Pass",

"Failure Mode",

"DateAdded",

"DateRetired",

"ReasonRetired",

"Make",

"Manufacturer",

"ModelNo",

"OccupancyType",

"InspectionFrequency",

"New Barcode",

"UserDef3"

FROM JSON (wrap on) "root";

[root]:

LOAD TEXT ([Barcode]) AS [Bascom Barcode],

[Username] AS [Bascom Username],

Date([Inspection Date]) AS [Get Date],

    [Inspection Time] AS [Bascom Inspection Time],

[Item Type] AS [Bascom Item Type],

[Building] AS [Bascom Building],

[Floor] AS [Bascom Floor],

[Location] AS [Bascom Location],

[Room Number] AS [Bascom Room Number],

[Description] AS [Bascom Description],

[Comments] AS [Bascom Comments],

[Pass] AS [Bascom Pass],

[Failure Mode] AS [Bascom Failure Mode],

[DateAdded] AS [Bascom DateAdded],

[DateRetired] AS [Bascom DateRetired],

[ReasonRetired] AS [Bascom ReasonRetired],

[Make] AS [Bascom Make],

[Manufacturer] AS [Bascom Manufacturer],

[ModelNo] AS [Bascom ModelNo],

[OccupancyType] AS [Bascom OccupancyType],

[InspectionFrequency] AS [Bascom InspectionFrequency],

[New Barcode] AS [BascomNew Barcode],

[UserDef3] AS [UserDef3]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

[sunny_talwar]

Where is the script after this that creates the calendar

[hammermill21]

After that I have the master Calendar which I shared before:

And that's it

MinMaxDate:

Load

min([Get Date]) as MinDate,

    max([Get Date]) as MaxDate

resident [root];

Let vMinDate = Peek('MinDate',0,'MinMaxDate'); 

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate');

Let vToday = $(vMaxDate);

drop table MinMaxDate;

TempCal:

Load

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

autoGenerate($(vMaxDate)-$(vMinDate)+1);

Mastercalendar:

Load

Date(TempDate) as [Get Date],

    year(TempDate) as Year,

    month(TempDate) as Month,

    monthname(TempDate) as MonthYear,

    day(TempDate) as Day,

    weekday (TempDate) as WeekDay,

    week(TempDate) as Week,

    year(yearstart(TempDate,0,4)) as FiscalYear,   

'Q' & ceil(month(TempDate)/3) as Quarter,    

InYearToDate(TempDate,$(vToday),0) * -1 as CurYTDFlag,

InYearToDate(TempDate,$(vToday),-1) * -1 as LastYTDFlag

resident TempCal;

drop table TempCal;

[sunny_talwar]

All this look pretty usual. I am not sure I know why the dates are getting dropped