topic Re: Date with string problem in QlikView

Date with string problem

AH — Wed, 24 Feb 2016 23:40:03 GMT

Hi,

I have a Month column that contains Months value Like this 'January', 'February' and so on. I have a Year field that contains Year Value Like this '2014', '2015' and so on.

I would like to tie this two column and make a date field and then calculate the max date from this field.

The problem is when i concatenate this two field and make one date field i cant calculate max date from that date field. I think the reason behind is, i am concatenating string and number.

Could anyone help me out this problem?

Thanks,

Ahmed100

Re: Date with string problem

sunny_talwar — Wed, 24 Feb 2016 23:42:33 GMT

Try this:

MakeDate(YearField, Num(Month(Date#(MonthField, 'MMMM'))), 1) as Date

Re: Date with string problem

johnw — Wed, 24 Feb 2016 23:49:34 GMT

I don't think being a number would stop the concatenation. Works fine for me like this:

date#('February ' & 2015,'MMMM YYYY')

So in the script, I'd do something like this:

,date#([Month] & ' ' & [Year],'MMMM YYYY') as [Month Year]

And then just this as the expression:

max([Month Year])

edit: date(max([Month Year],'MMM YYYY')

Re: Date with string problem

AH — Thu, 25 Feb 2016 00:08:03 GMT

Hi John, Thanks a lot!

While calculating the max i am getting integer value but if i use date function before max then i am getting date format like this 1/1/2016. but i would like to keep it like Jan 2016.

Thanks again.

Regards,

Ahmed100

Re: Date with string problem

johnw — Thu, 25 Feb 2016 00:10:03 GMT

Ah, right. Probably this?

date(max([Month Year],'MMM YYYY')

Re: Date with string problem

sunny_talwar — Thu, 25 Feb 2016 00:10:39 GMT

Here is a sample to play around with:

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

Table:

LOAD *,

MonthName(Date) as MonthYear;

LOAD *,

MakeDate(Year, Num(Month(Date#(Month, 'MMMM'))), 1) as Date;

LOAD * Inline [

Year, Month, Sales

2014, January, 343

2014, February, 420

2014, March, 492

2014, April, 234

2014, May, 542

2014, June, 432

2014, July, 654

2014, August, 234

2014, September, 323

2014, October, 653

2014, November, 653

2014, December, 658

];

Re: Date with string problem

AH — Thu, 25 Feb 2016 00:16:49 GMT

Hi Sunny, for some reason your solution didnt show up before Johns post! . I dont know what happened.

But thank you so much for your help. You have been very helpful before as well.

Re: Date with string problem

sunny_talwar — Thu, 25 Feb 2016 00:20:40 GMT

As long as you get to what you are looking for, we don't mind who get the right answer. If you still feel my answer is useful, you can mark it as a helpful answer (but you don't have to)

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: Date with string problem

johnw — Thu, 25 Feb 2016 00:39:35 GMT

Yeah, strange, I didn't see it before I posted either, or even after I posted for quite some time.

(edit: Ah, the forum updates the time when I update the message.)

Re: Date with string problem

sunny_talwar — Thu, 25 Feb 2016 00:42:02 GMT

It popped up from no where? Strange, I was not even put for moderation

Re: Date with string problem

AH — Thu, 25 Feb 2016 00:55:19 GMT

Hi Sunny,

It could be my mistake i am not sure though. When i saw a notification for the first time i clicked and Johns post showed up so i thought he is the first person replied to the post. And when i replied to him the page get refreshed and then i saw your two post before Johns post.

Thanks & regards,

ahmed100

Re: Date with string problem

sunny_talwar — Thu, 25 Feb 2016 01:03:14 GMT

Its all good ahmed .

Re: Date with string problem

Anonymous — Thu, 25 Feb 2016 05:02:37 GMT

MakeDate(Year, Num(Month(Date#(Month, 'MMMM'))), 1) as Date; try this one it is working

Re: Date with string problem

AH — Fri, 26 Feb 2016 00:52:24 GMT

Hi Guys, I am having a little trouble calculating Previous Year sales and Year over Year Sales. In the set analysis, Previous year sales calculation I am using a variable (vPreviousYear) which is basically (Current/Max Year - 1) and also trying to use Month in that set analysis but its not working.

My Calculation is for Previous Year: sum({<[Year]={$(vPreviousYear)}, [Month]={<=$(vMaxMonth)}>} Sales)

This calculation will basically help me to select and Year and month and get the value of Current selected Year and Month value and also for the Previous year sales value for the same month.

Please find attach file and let me know if you have any question.

Thanks,

ahmed100

Re: Date with string problem

sunny_talwar — Fri, 26 Feb 2016 01:00:05 GMT

Is this what you are looking for:

Expressions:

=Sum(Sales)

=If(Sum(Sales) > 0, Above(Sum({1}Sales)))

=[Current Year Sales]/[Previous Year Sales] - 1

Re: Date with string problem

AH — Fri, 26 Feb 2016 01:17:18 GMT

Hi Sunny,

Thanks for the reply but its not working when i select Month January and Year 2016. I should get January 2016 as the current selection and January 2015 as previous year sales.

Could you attach the application here?

Thanks,

ahmed100

Re: Date with string problem

sunny_talwar — Fri, 26 Feb 2016 01:20:54 GMT

May be this for your second expression:

=If(Sum(Sales) > 0, Above(Sum({<Year>}Sales)))