Differences in Dates with SQL and LOAD

[#1]

I have been looking at changing some of SQL Querys in to Qlikview LOADS but have an odd problem

In SQL I have this

CONVERT(VARCHAR(7), CreateDate, 120) AS CreatedDateYYYYMM

In the LOAD I have this

Date(CreateDate, 'YYYY-MM') AS CreatedDateYYYYMM

I have a chart with the CreatedDateYYYYMM field as a Dimension and this works as planned with the SQL statement i.e. it displays the number of calls for each month but when using the LOAD it displays each call separately in each month rather than display the total for the month.

The date output for each looks the same i.e. 2013-01, 2013-02 etc the only difference I can see is that in the table previewer the CONVERT one is displayed left formatted and the DATE one is right formatted.

Any ideas why this is?

[Colin-Albert]

The reason you are seeing each day is that date(date_field, 'YYYY-MM) will alter the text component but leave the numeric part of the dual field unchanged.

Try monthstart(date_field) instead.

[#3 · Accepted solution]

I tried this

DATE(MONTHSTART(CreatedDate), 'YYYY-MM')

and this gave me the output for each month on the chart.

The only problem now is that the monthly totals dont match up with the previous chart and are for the most part less than the prevvious chart.

Thanks for all the help and advice...as always I've learned more about QV than I had bargained for.

[#4]

I figured out what I'd done to get the totals wrong...wasn't anything to do with the dates...was just something else that got changed while trying to figure why this wasn't working and its corrected now.

Thanks again