Month Year Duplicate date conversion problem

[Katherine]

Hi, 

I'm trying to bucket incidents by MonthYear. 

LOAD
*
Month(IncidentDate) as Month,
Year(IncidentDate) as Year,
Date(IncidentDate,'MMM-YY') as MonthYear

However, when I do so using the above script it looks like it is converting each individual date into a MonthYear value, with lots of duplicates. You can see the issues pretty clearly in the attached chart. 

Any suggestions? I tried to use MonthStart because I read somewhere that that would help eliminate the duplicates, but then the calculation didn't work at all (null values).

Date(MonthStart(IncidentDate,'MMM-YY')) as MonthYear

[sunny_talwar · Accepted solution]

I think you misplaced the parenthesis.. try this

Date(MonthStart(IncidentDate),'MMM-YY') as MonthYear

[qliksus]

Does it show the similar duplicate value if you take Month Year in a list box?? If Jan-19 is shown repeatedly means QV is treating it as different value instead of one

[PrashantSangle]

try
1: Date(monthStart(IncidentDate),'MMM-YY')
2: Date(monthEnd(IncidentDate),'MMM-YY')
3: MonthName(IncidentDate)

Regards,

[Katherine]

Oh the joy and pain of a single parenthesis - thank you, that fixed it!