topic Re: Month Year Duplicate date conversion problem in QlikView

Month Year Duplicate date conversion problem

Katherine — Sat, 16 Nov 2024 03:46:12 GMT

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

Re: Month Year Duplicate date conversion problem

sunny_talwar — Thu, 25 Apr 2019 01:18:58 GMT

I think you misplaced the parenthesis.. try this

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

Re: Month Year Duplicate date conversion problem

qliksus — Thu, 25 Apr 2019 09:13:30 GMT

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

Re: Month Year Duplicate date conversion problem

PrashantSangle — Thu, 25 Apr 2019 09:21:07 GMT

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

Regards,

Re: Month Year Duplicate date conversion problem

Katherine — Thu, 25 Apr 2019 16:13:29 GMT

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